How can I protect username.text from SQL injections aka '?

[trend]

Hello, I have 2 text fields... username and password.

 

I want to make sure there are no ' or " or '' or "" or """ or '''.. you see what I am saying in the username or password field.

 

I have been trying soemthing like this:

       SafeUsername = Replace(username.Text, "'", "''")
       SafePassword = Replace(password.Text, "'", "''")

but that isn't seeming to work.

 

Any ideas?

 

 

thanks!

Lee

[kejpa]

Use parameters for your query

 

/Kejpa

[Joe Mamma]

Use parameters for your query

 

/Kejpa

(almost) Never build sql!!!!

 

if I see built sql in a propspective hire's portfolio, I go to the next.

[trend]

(almost) Never build sql!!!!

 

if I see built sql in a propspective hire's portfolio, I go to the next.

 

I do not understand..

 

 

 

Also, the way I am logging users on, should be safe (right?).. The username/password is not a username/password in a table.. but I use the username/password to log users on to the db.. aka we use ms sql logins (so we have hundreds of logins).

 

So really... if someone did sql inject something, it wouldn't do anything.. because sql wouldn't log them in... they would just get a nasty error msg afterwords.. but i don't want that because it tells some details about our code...

 

 

Ideas?

[VagabondSW]

I do not understand..

 

 

 

Also, the way I am logging users on, should be safe (right?).. The username/password is not a username/password in a table.. but I use the username/password to log users on to the db.. aka we use ms sql logins (so we have hundreds of logins).

 

So really... if someone did sql inject something, it wouldn't do anything.. because sql wouldn't log them in... they would just get a nasty error msg afterwords.. but i don't want that because it tells some details about our code...

 

 

Ideas?

While Joe Mamma is correct and kejpa gave you the correct answer, I will try and explain how to implement it.

 

Dim paramName As String
Dim paramValue As Object
Dim sqlSelect As String

paramName = "@username"
paramValue = username.Text
sqlSelect = "SELECT username FROM users WHERE username=@username;"

 

You would then use that query to get your scalar value. I have an example using the ExecuteScalar method of an OleDbCommand object, but you could adapt it for an SqlDbCommand object without too much trouble.

 

Public Function GetScalarValue(ByVal sqlSelect As String, _
 ByVal paramName As String, _
 ByVal paramValue As Object) _
As Object

 Dim objValue As Object
 Dim command = New OleDbCommand(sqlSelect, m_connection)
 
 command.Parameters.Add(paramName, paramValue)
 Try
 m_connection.Open()
   objValue = command.ExecuteScalar()
 Catch ex As Exception
   Dim errorMessage As String = ex.Message
   Throw
 Finally
   m_connection.Close()
 End Try

 Return objValue
End Function

 

That should get you going.

[Diesel]

Depending on the database server, you could use stored procedures.

 

Also, you should encrypt the password (SHA1 is a nice algorithm), and possibly salt it (create a random value that is used to encrypt and save it in the database).

 

When checking the password, simply encrypt the given answer (don't forget the salt) and check against the database field.

 

If you want more info, I have an example of sha1 encryption at work...Just tell me.

 

Also, Joe Mamma, what was meant about built sql? I assumed it meant that the sql was actually included within the code? As opposed to in the database server?

[Joe Mamma]

Also' date=' Joe Mamma, what was meant about built sql? I assumed it meant that the sql was actually included within the code? As opposed to in the database server?[/quote']

I mean building a variable string of sql:

 

string sql = "select aField from aTable where aKey = " + aSomeValue.ToString();

 

 

nothing inheriently wrong in my opinion with executing text commands as opposed to stored procedures, but building a string like above can lead to unforeseen error and risk. Yes, they can be programmed around but why not just eliminate with parameters.

 

Plus parameterization makes commands optimal as the sql doesnt change. this is particularly important in looped operations.

[trend]

I mean building a variable string of sql:

 

string sql = "select aField from aTable where aKey = " + aSomeValue.ToString();

 

 

nothing inheriently wrong in my opinion with executing text commands as opposed to stored procedures, but building a string like above can lead to unforeseen error and risk. Yes, they can be programmed around but why not just eliminate with parameters.

 

Plus parameterization makes commands optimal as the sql doesnt change. this is particularly important in looped operations.

 

I tried using this.. but it looks like if asomevalue.text field has a ' in there, it still causes an error..

 

 

Also

command.Parameters.Add(paramName, paramValue)

doesn't work either.. I had test1' as paramvalue and I still caused a fault..

 

(Am I doing something wrong maybe?.. I would love for someone to look over my code in a PM)

Any ideas?

 

Maybe I should just only allow certain characters in the txtbox?

 

thanks

Lee

[trend]

Maybe the reason the reason the ' is still causing a fault is because I am using a stored procedure:

Dim MyCommand As New OleDbCommand(("exec Retreave_Failed_Logins '" & UsernameTry & "', '" & Date.Now.Date & "'"), MyConnection)  '& " and Time = " & date.Now.ToLongTimeString

[PlausiblyDamp]

You are still using concatenation to build your query - Post 5 above shows you the correct way to do this.

[Joe Mamma]

this:

string sql = "select aField from aTable where aKey = " + aSomeValue.ToString();

is an example of what not to do. . . aSomeValue was just a dummyvariable. . .

 

 

SQL server???

 

 

myCommand.CommandText = "select aField from aTable where aKey = @aValue"

myCommand.Parameters("@aValue").Value = ATextBox.Text.Trim())

 

now it is immune to escape and syntax characters as well as sql injection.

[VagabondSW]

Maybe the reason the reason the ' is still causing a fault is because I am using a stored procedure:

 

What is the error message you are receiving?

 

Can you please post your ConnectionString?

 

Can you please post your ConnectionObject declaration?

[trend]

What is the error message you are receiving?

 

Can you please post your ConnectionString?

 

Can you please post your ConnectionObject declaration?

 

I get this error(It is catched of course):

 

Banned user lookup failedAn OleDbParameter with ParameterName '@Usern' is not contained by this OleDbParameterCollection. Database Open, Logged attempt to DB, Database Closed, Logged attempt to DB,

 

when I run this:

  Private Function Find_Failed_Logins()
       Dim MyConnection As New OleDbConnection("Provider=SQLOLEDB.1;User Id=WeGotItRight_website;Password=sOnicgAtewa1y;database=WeGotItRight_website;server=secure.wegir.com;Use Encryption for Data=False")
       Dim UsernameTry As String = SafeUsername
       'Dim paramValue As Object = "@username"
       'Dim MyCommand As New OleDbCommand("SELECT * FROM Login_Attempts WHERE Username = '" & UsernameTry & "' and Payload = 'Auth Failed' and Date = '" & Date.Now.Date & "'", MyConnection) '& " and Time = " & date.Now.ToLongTimeString


       Try

           'Dim MyCommand As New OleDbCommand(("exec Retreave_Failed_Logins '" & UsernameTry & "', '" & Date.Now.Date & "'"), MyConnection)  '& " and Time = " & date.Now.ToLongTimeString
           Dim sqlselect As String = "exec Retreave_Failed_Logins @Usern, '" & Date.Now.Date & "'"
           Dim MyCommand As New OleDbCommand(sqlselect, MyConnection)  '& " and Time = " & date.Now.ToLongTimeString
           MyCommand.Parameters("@Usern").Value = UsernameTry.Trim()
           Dim nowdate As DateTime = DateTime.Parse(Date.Now.ToLongTimeString)
           Dim faileddate As DateTime '= DateTime.Parse("6:51:49 AM")


           MyConnection.Open()
           Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader()
           While MyReader.Read
               faileddate = DateTime.Parse(MyReader("Time"))
               Dim TimeDiff As New TimeSpan(nowdate.Ticks - faileddate.Ticks)

               If TimeDiff.TotalMinutes < 30 Then
                   'Response.Write(TimeDiff.TotalMinutes & ";")
                   NumOfRec = NumOfRec + 1
               End If

           End While

           validationwindow.Text = validationwindow.Text & "User failed to login: " & NumOfRec & " times within 30 minutes. " & vbNewLine

           MyConnection.Close()
       Catch
           connectionstatus.Text = connectionstatus.Text & "Banned user lookup failed" & Err.Description & vbNewLine
       End Try

       Return NumOfRec

   End Function

 

I have tried: UsernameTry.Trim() and UsernameTry

 

In another fuction we do:

SafeUsername = username.Text

SafePassword = password.Text

(I have also tried the above with .trim()s on both)

 

 

any ideas?

Edited August 10, 2005 by trend

[trend]

And yes, I have tried @Usern and '@Usern'

[Joe Mamma]

{ oops. . . deleted a bad reply!!! }

 

please stand by. . .

Edited August 10, 2005 by Joe Mamma

[Joe Mamma]

you are doing a stored proc. . .

 

Dim MyCommand As New OleDbCommand("Retreave_Failed_Logins", MyConnection)

MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@Usern", UsernameTry.Trim())

MyCommand.Parameters.Add("@ADate", DateTime.Now)

 

 

that should work

[Joe Mamma]

oh yeah. . .

 

GODDAMN THE ANGELS!!!!

[trend]

you are doing a stored proc. . .

 

Dim MyCommand As New OleDbCommand("Retreave_Failed_Logins", MyConnection)

MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Parameters.Add("@Usern", UsernameTry.Trim())

MyCommand.Parameters.Add("@ADate", DateTime.Now)

 

 

that should work

 

Yep, You are a genious!

 

That fixed my 2 functions... Thanks for all the help.

 

One more question though, I almost started another thread.. but figured this sorta goes along the same lines.

 

I have a function that has this:

 

Dim MyConnection As New OleDbConnection("Provider=SQLOLEDB.1;User Id=" & SafeUsername & ";Password=" & SafePassword & ";Initial Catalog=database;server=databaseserver.com;Use Encryption for Data=False")

 

I am doing this in another function:

       SafeUsername = username.Text.Trim()
       SafePassword = password.Text.Trim()

 

But the above code will still error out when someone does something like username = hacker'

 

I do not believe that a user could use this 'hole' to get into the db.. And even though I am catching for errors like this.. I still would like to learn how to plug a hole like this :)

 

 

thanks again for the help!

 

Lee

[Joe Mamma]

but that one isnt susceptibe to sql injevtion, just do a replace of ' with '' (single quotes with double single quotes)