Problem with validating login (all around sql statements)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with validating login (all around sql statements)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-13 : 08:43:09

suavek writes "Hi Sql Team..

I got a fairly hard question...

Here it goes,

i have a login name and a password text box on an ASP page,

my sql statement generated from this page is something like this

Sql="SELECT * FROM UserTable WHERE fldLogin='"&txtLogin&"' AND fldPassword='"&txtPassword&"'"

Now here is the problem,

If i enter a value for login name such as "' OR ''=' " (please disregard the double quotes around the string
and i enter "' OR ''='" for the password

the sql query is still correct, but its a way to bypass the actual screen, Sql in that case = "SELECT * FROM UserTable WHERE fldLogin='' OR ''='' AND fldPassword='' OR ''=''"

as you can see the SQL query will always return a record..

How can i go about fixing this?

i want to be able to also eventually include the " ' " somewhere as a login name (just for example)

what do i do?

This goes for any other SQL queries i might have on my ASP page..

Is there a default function to check for valid entries?

what can i do? should i put the values into Double Quotes (") ?

Please help

Regards,
Suavek"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-13 : 08:56:16

STORED PROCEDURES!!!

What you are describing here is very basic, vanilla SQL Injection. Because you are constructing the SQL statement on the fly, you will always have problems avoiding possible SQL injection attacks.

The easiest way to circumvent this problem is to create a stored procedure to handle the login process. Something like:

CREATE PROCEDURE myLogin @login varchar(20), @password varchar(30) AS
SET NOCOUNT ON
SELECT * FROM UserTable WHERE fldLogin=@login AND fldPassword=@password

You can modify your existing code to read:

SQL="EXECUTE myLogin @login='" & txtLogin & "', @password='" & txtPassword & "' "

But it would be better to use an ADO Command object:

set cmd=CreateObject("ADODB.Command")
cmd.CommandType=4 'or use the adCmdStoredProc constant
cmd.ActiveConnection=ADOConnectionObject 'put your actual ADO connection object here
cmd.CommandText="myLogin"
cmd.Parameters.Append cmd.CreateParameter("@login", advarchar, adparaminput,20,txtLogin)
cmd.Parameters.Append cmd.CreateParameter("@password", advarchar, adparaminput,30,txtPassword)
cmd.execute()

This not only makes your SQL less hackable and breakable, but also improves performance by allowing the stored procedure to be properly cached in SQL Server. In any event, by using a stored procedure and passing parameters you'll remove the chance for someone to hack at the syntax of the SELECT statement. The parameters @login and @password cannot alter the statement itself. If some kind of hacked SQL expression was passed, it would simply return no rows when executed.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-13 : 17:40:08

I fully agree with what Rob has presented, and I'd like to add one option. On something as significant (and as easily verified) as a login, I'd suggest you also do a secondary check after the stored procedure to verify the contents of the result set. Something like:

If RS.Fields("LoginName") = Request.Form("LoginName") Then
ContinueFlag = True
Else
ContinueFlag = False
'Redirect to error notice
End If

You might also re-validate the password if it isn't hashed.

------------------------------------------------------
The more you know, the more you know you don't know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources