Preventing SQL Injection [modified]

ASPnoob

Hi, I'm atempting to prevent SQL injection by incorporating the function below into my code, but I'm not sure how to change my select statement to make it work. Below is the function I'm intending to use and below it is my code.

function killChars(strWords)
dim badChars
dim newChars

badChars = array("select", "drop", ";", "--", "insert", "delete", "xp_")
newChars = strWords

for i = 0 to uBound(badChars)
newChars = replace(newChars, badChars(i), "")
next

killChars = newChars

end function

Private Sub lblRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblRegister.Click
Dim myConnection As OdbcConnection
Dim myCommand As OdbcCommand
Dim strInsert As String
Dim strSQL As String
strSQL = String.Empty

     myConnection = New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=myServer;Database=myDB;User=myUser; Password=myPW;Option=3;")
        strSQL = String.Format("SELECT UserName FROM myTable WHERE (UserName='{0}');", txtUserName.Text)
        myCommand = New OdbcCommand(strSQL, myConnection)
        myCommand.CommandType = CommandType.Text
        myConnection.Open()
      
        Dim result As Integer = CType(myCommand.ExecuteScalar,Integer)
        ' If record count > 0, then UserName already exists in the database
        If result > 0 Then
           lblMessage.Text = "User name already exists in the database"
       Else
           strInsert = "INSERT into myTable (Password,UserName)values (?,?)"
           Dim myCommand1 As OdbcCommand = New OdbcCommand(strInsert, myConnection)
           myCommand1.Parameters.Add(new OdbcParameter("@Password", txtPassword.Text))
           myCommand1.Parameters.Add(new OdbcParameter("@UserName", txtUserName.Text))
           
           Dim result1 As Integer = myCommand1.ExecuteNonQuery()
       End If
           'close the connection
           myConnection.Close()

End Sub

I don't know how to modify the lines

strSQL = String.Format("SELECT UserName FROM myTable WHERE (UserName='{0}');", txtUserName.Text)

and

strInsert = "INSERT into myTable (Password,UserName)values (?,?)"

to make the KillChars function work. Any suggestions will be grately appreciated, thank you in advance for your help. -- modified at 3:07 Wednesday 20th June, 2007

Arun Immanuel

ASPnoob wrote:

I don't know how to modify the line strSQL = String.Format("SELECT UserName FROM myTable WHERE (UserName='{0}');", txtUserName.Text) to make the KillChars function work.

You can use the killChars like this: strSQL = String.Format("SELECT UserName FROM myTable WHERE (UserName='{0}');", killChars(txtUserName.Text)) But you should use killChars to prevent SQL Injection. The best way you can do this is by using stored procedure. I would suggest you to read this http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp[^].

Regards, Arun Kumar.A

badgrs

I could be wrong on this but I thought the whole point of using parameters (as you are doing) was to prevent SQL injection, ie ADO.NET would take care of it for you so you wouldn't need your own function.

Sylvester george

The following link will help you how to do http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson06.aspx[^]

Regards, Sylvester G sylvester_g_m@yahoo.com