finding hidden chars in sql query

I am working on a login system in flex and asp. I am encrypting the password before it is inserted it into the SQL database. But then when i do SELECT statement with username and encrypted password it returns 0 users found.
I outputted the sql statement and they passwords look excatly the same. But the SQL Select count(*) returns a 0.

link to the encrypting is used:
http://www.4guysfromrolla.com/webtech/110599-1.2.shtml

other information:
script language: asp + flex
database: sql server 2005

So i am thinking that their are hidden chars in the password. Is their a way to check this or even convert/exclude them ?
Any links or tips would be very helpfull

Use the sample to solve your issue...

Alter Function dbo.En_De_Crypt(@.Input varchar(max), @.Key int ) Returns Varchar(Max) as

Begin

Declare @.Len as Int;

Declare @.I as Int;

Declare @.Output as Varchar(max)

Select @.Len = Len(@.Input), @.I =1, @.Output=''

Declare @.Number Table (N int);

While(@.I<=@.Len)

Begin

Insert into @.Number Values(@.I);

Set @.I = @.I + 1;

End

Select @.Output = @.Output + Char(Ascii(Substring(@.Input,N,1)) ^ @.Key)

From @.Number

return @.Output

End

Go

Create Table #Passwords(

Password varchar(20)

);

Insert Into #Passwords Values(dbo.En_De_Crypt('One1234$$',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('M1cr0$0ft',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('Or@.c1e',100));

Insert Into #Passwords Values(dbo.En_De_Crypt('@.pp1e',100));

Select dbo.En_De_Crypt(Password,100) ,Password From #Passwords

/*

Orginal value Decrypted Value

One1234$$ + _VWP@.@.

M1cr0$0ft )Up'16T@.T '10

Or@.c1e +_$p_par

@.pp1e $__U_par */

--None of the query will return the data here..

Select Count(*) From #Passwords Where Password = '+

_VWP@.@.'

Select Count(*) From #Passwords Where Password = ')Up'16T@.T '10'

Select Count(*) From #Passwords Where Password = '?_p$'

Select Count(*) From #Passwords Where Password = '$__U_par'

--Use the following query to get the result..

Select Count(*) From #Passwords Where dbo.En_De_Crypt(Password,100)='M1cr0$0ft'

|||thnx Manivannan for the reply.
i will try it out.

finding active users from sysprocesses

I am trying to find a select on sysprocesses that would list all the active
logins. An active login is a login that has a TSQL statment being executed
on the server,

This didnt work to well! Any ideas. Thanks in advance.

select sp.loginame,
-- more columns
from master..sysprocesses sp
where sp.status not in ('sleeping','background' )
order by 1You can use the system stored procedure sp_who & sp_who2 for these purposes.

--
- Anith
( Please reply to newsgroups only )|||kr (zzb26 (at) email.com) writes:
> I am trying to find a select on sysprocesses that would list all the
> active logins. An active login is a login that has a TSQL statment being
> executed on the server,
> This didnt work to well! Any ideas. Thanks in advance.
> select sp.loginame,
> -- more columns
> from master..sysprocesses sp
> where sp.status not in ('sleeping','background' )
> order by 1

In which way did it not work? In any case, you definitely want to add
"or opentrn > 0". A process which has a open transaction is active,
even if it is sleeping.

I have a lock-monitoring routine, and the condition I use is

upper(p.cmd) <> 'AWAITING COMMAND'

I also check whether sysprocesses.blocked > 0.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Find Windows/SQL login used by RS

What is the 'supported' way to find out which Windows/SQL login has been
configured for RS to use when it connects to the report server database?
RSconfig.exe doesn't seem to have a read-only option.
LinchiThere isn't one. You can find out by looking at the RSExec role in the
database your using and checking it's members. If you see machine\ASPNET
and builtin\Network Service then you're using the service credentials.
Otherwise you'll see either a domain account or a sql login.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
news:%23liDwCoeEHA.3124@.TK2MSFTNGP09.phx.gbl...
> What is the 'supported' way to find out which Windows/SQL login has been
> configured for RS to use when it connects to the report server database?
> RSconfig.exe doesn't seem to have a read-only option.
> Linchi
>|||Thanks! I guess that's a workaround. There really should be a page in Report
Manager to list all the Report Server configurations.
Linchi
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:eY4Sf$oeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> There isn't one. You can find out by looking at the RSExec role in the
> database your using and checking it's members. If you see machine\ASPNET
> and builtin\Network Service then you're using the service credentials.
> Otherwise you'll see either a domain account or a sql login.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Linchi Shea" <linchi_shea@.NOSPAM.ml.com> wrote in message
> news:%23liDwCoeEHA.3124@.TK2MSFTNGP09.phx.gbl...
> > What is the 'supported' way to find out which Windows/SQL login has been
> > configured for RS to use when it connects to the report server database?
> > RSconfig.exe doesn't seem to have a read-only option.
> >
> > Linchi
> >
> >
>

Find time interval between dates

Hi, all. I've got a classic ASP app that I've been tasked with modifying to
track users' IP addresses. If they fail to login, we want to record their IP
and the date/time when they failed. If they fail more than 5 times within 10
minutes, we want to lock them out for 5 minutes. Below are the two SQL
Server 2000 tables I've created.
The problem I'm having is this: How do you figure out if user has failed 5
times within the last 10 minutes. If you believe there's a better way to
design the tables for this task, please let me know. Thank you :)
CREATE TABLE [tblHits] (
[hitID] [int] IDENTITY (1, 1) NOT NULL ,
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
(
[hitID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
(
[IP]
) REFERENCES [tblIPs] (
[IP]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblIPs] (
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
(
[IP]
) ON [PRIMARY]
) ON [PRIMARY]
GOHi
smalldatetime data type has only the accuracy of 1 minute, so you might be
working between 9 and 11 minutes. The data type datetime might be more
appropriate.
Unless this is an intranet application, there is no guarantee that the IP
address could not be the source for multiple users. Corporates have large
proxy servers that do not expose the internal source IP. You might make a
lot of users unhappy if one of them fails to login correctly.
That aside. The following will return the number of hits in the last 10
minutes:
SELECT
COUNT(*)
FROM
tblHits
WHERE
dateAdded > DATEADD(mi, -10, GETDATE())
AND IP = @.YourCurrentIPAddress
Regards
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Hi
. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
>
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
you get the idea and an modify the query.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Thank you, Michael and Uri. You've got some very creative solutions. By the
way, the point about public computers or proxy servers is a valid one; we
discussed it, but the powers-that-be wanted to go ahead and do this anyway.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhYgKlbIGHA.3144@.TK2MSFTNGP10.phx.gbl...
> Hi
> . If you believe there's a better way to
>
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:00','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:20','20000610 10:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:23','20000610 10:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:27','20000610 10:45')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 11:57','20000610 12:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:01','20000610 12:04')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:04','20000610 12:40')
> SELECT
> StartTimeCon,
> ISNULL(
> (SELECT MIN(EndTimeCon)
> FROM tblConnection AS S3
> WHERE S3.StartTimeCon >= S1.StartTimeCon
> AND ISNULL(
> DATEDIFF(
> minute,
> S3.EndTimeCon,
> (SELECT MIN(StartTimeCon)
> FROM tblConnection AS S4
> WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
> EndTimeCon) AS EndTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
>
>
> Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
> you get the idea and an modify the query.
>
>
> "curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
> news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
>