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

T

trend

Well-known member
Joined
Oct 12, 2004
Messages
171
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:
Code: 
        SafeUsername = Replace(username.Text, "", "")
        SafePassword = Replace(password.Text, "", "")
but that isnt seeming to work.

Any ideas?


thanks!
Lee
 
Joe Mamma said:
(almost) Never build sql!!!!

if I see built sql in a propspective hires portfolio, I go to the next.
Click to expand...

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 wouldnt do anything.. because sql wouldnt log them in... they would just get a nasty error msg afterwords.. but i dont want that because it tells some details about our code...


Ideas?
 
trend said:
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 wouldnt do anything.. because sql wouldnt log them in... they would just get a nasty error msg afterwords.. but i dont want that because it tells some details about our code...


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

Code: 
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.

Code: 
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.
 
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 (dont 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?
 
Diesel said:
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?
Click to expand...
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.
 
Joe Mamma said:
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.
Click to expand...

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)
doesnt 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
 
Maybe the reason the reason the is still causing a fault is because I am using a stored procedure:
Code: 
Dim MyCommand As New OleDbCommand(("exec Retreave_Failed_Logins " & UsernameTry & ", " & Date.Now.Date & ""), MyConnection)  & " and Time = " & date.Now.ToLongTimeString
 
this:
string sql = "select aField from aTable where aKey = " + aSomeValue.ToString();
Click to expand...
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.
 
trend said:
Maybe the reason the reason the is still causing a fault is because I am using a stored procedure:
Click to expand...

What is the error message you are receiving?

Can you please post your ConnectionString?

Can you please post your ConnectionObject declaration?
 
VagabondSW said:
What is the error message you are receiving?

Can you please post your ConnectionString?

Can you please post your ConnectionObject declaration?
Click to expand...

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:
Code: 
   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?
 
Last edited by a moderator:
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 said:
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
Click to expand...

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:

Code: 
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:
Code: 
        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
 
Back
Top