Serious problem with SQL database query (please help)

[esposito]

Hello, I have a serious problem dealing with database interrogation through SQL statements. To interrogate my database of employees by surname, I use the following code:

 

         Dim mykeyword As String = txtKeyWord.Text
         Dim MySQL as string = "Select * from employees WHERE surname = '" & mykeyword & "' Order by id_employee"

         Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & server.mappath("employees.mdb") & ";")
         Dim ds as DataSet=New DataSet()

         Dim Cmd as New OleDbDataAdapter(MySQL,MyConn)
         Cmd.Fill(ds,"employees")

         myDataGrid.Datasource=ds.Tables("employees").DefaultView
         myDataGrid.DataBind()
         MyConn.Close()

 

Now, the problem arises when the user types one or more apostrophes (') in the TextBox for the keyword. The apostrophes interfere with the SQL string and the search simply fails.

 

I suppose I should check the content of the textbox before executing the query, to make sure there are no invalid characters, but I don't know how to look for a specific character in a string and replace it.

 

Besides, I think that it would be absurd to prevent the user from typing apostrophes because that character is so common that it would become a serious limitation.

 

I suppose I am not the first person who has encountered such a problem, so if you know the solution, please help me.

 

Thanks in advance.

[PlausiblyDamp]

You would be much better of using parameters rather than concatenating strings - parameters are much easier and less prone to common exploits (Search for SQL Injection for an example).

 

The following should work - not near vs at the moment so I haven't tested it.

         Dim mykeyword As String = txtKeyWord.Text
         Dim MySQL as string = "Select * from employees WHERE surname = ?  Order by id_employee"

         Dim myConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & server.mappath("employees.mdb") & ";")
         Dim ds as DataSet=New DataSet()

         Dim Cmd as New OleDbDataAdapter(MySQL,MyConn)
         Cmd.Parameters(0) = myKeyword
         Cmd.Fill(ds,"employees")

         myDataGrid.Datasource=ds.Tables("employees").DefaultView
         myDataGrid.DataBind()
         MyConn.Close()

Edited March 30, 2007 by PlausiblyDamp

[esposito]

Thanks. I'll do some research into how to use parameters.

[VBAHole22]

The parameter suggestion is a good one. This has always been a major problem for the db world. Another, less desirable, option is to use a find and replace function on the incoming parameter to replace the single quote with something else. You can use Regular Expressions for this. The trouble with this, and any other method you use, is that you need to be sure you use it when the data goes in AND when it comes out. Or you may find that single quote causing more issues.

 

Up until now I have been removing the singel quote by searching for it like so

 

'Test for and Replace single single quote
                           Dim i As Integer
                           i = InStr(vNAME, "'", CompareMethod.Text)
                           If i <> 0 Then
                               vNAME = vNAME.Replace("'", "")
                           End If

 

 

But this causes all sorts of headaches when you go to look for something later. Of course this deals with filenames and folks shouldn't be using that character anyway. If you use this method you can trap when this occurs and give people a hard time about using the apostrophe in a filename.

But if you are dealing with human names you can't very well go changing people's names for them. Especially since this will differently affect the Irish and you may find yourself in some hot water at the pub!

 

 

I will be switching to the parameter code immediately. Thanks for the tip.

[esposito]

Dear VBAHole22, I managed to sort out my problem with a single line of code, which runs as follows:

 

Dim mykeyword As String = Replace(txtKeyWord.Text, "'", "''")

 

You don't need to use the same Replace function in input. Placing it in output is enough to avoid any trouble. Try to believe.

[VBAHole22]

Actually what I was referring to is, How do records get in your table?

 

If you have something somewhere that says:

 

Dim MyName, MySQL as String

MyName = Me.TextBox1.Text

MySQL = "INSERT INTO MyTable (ID, Name) VALUES (ID,'" & MyName & "')"

'And a fine red-haired gentlemen enters his info

'You get

INSERT INTO myTABLE (ID, Name) VALUES (7,'Patty O'Shea')

 

An undesireable effect will unsue.

 

That was what I meant about being careful.

As they say, 'Garbage In , Garbage Out'

[Joe Mamma]

Dear VBAHole22, I managed to sort out my problem with a single line of code, which runs as follows:

 

Dim mykeyword As String = Replace(txtKeyWord.Text, "'", "''")

 

You don't need to use the same Replace function in input. Placing it in output is enough to avoid any trouble. Try to believe.

This is a bad work around.

 

Building SQL from user input is poor, poor practice.

It is an extreme security risk!!!

 

Parameters are not hard to use.

Parameters can make your code faster.

Parameters make your code secure.

 

Please I urge you to use them.

[VBAHole22]

You don't need to convince me.

 

I See The Light.

 

Parameterization is my Salvation.

[esposito]

How would you transform my search function using parameters?

 

This is a bad work around.

 

Building SQL from user input is poor, poor practice.

It is an extreme security risk!!!

 

Parameters are not hard to use.

Parameters can make your code faster.

Parameters make your code secure.

 

Please I urge you to use them.

[PlausiblyDamp]

Did you try the suggestion I posted near the top of this thread?

[VBAHole22]

This may not be exactly what you are looking for but here is the win app / Oracle version of what you are after. Oracle through .NET doesn't support the ? format. They use the :

But I don't know about SQL Server.

I realize this is a better way to get this done. But why? Well, I realize you can get strings with apostrophes in. But does the parameterization really prevent SQL injection? Does it do any 'cleaning'? :rolleyes:

 

 

 

 

Sub ParameterTest()
       Dim strInsert As String = String.Empty
       Dim objInsertCmd As New OracleCommand(strInsert, OracleConn)
       Dim P_Name As New OracleParameter
       Dim theName As String = "Micky O'Shanahan"

       Try
           With objInsertCmd
               .CommandType = CommandType.Text
               .CommandText = "INSERT INTO McTable (YerName) VALUES (:P_Name)"
               .Parameters.Add(P_Name)
           End With
           With P_Name
               .ParameterName = "P_Name"
               .DbType = DbType.String
               .Direction = ParameterDirection.Input
               .Value = theName
           End With

           OracleConn.Open()
           objInsertCmd.ExecuteNonQuery()
           OracleConn.Close()
           Console.WriteLine("Mickey is in")
       Catch ex As Exception
           Console.WriteLine(ex.Message)
       Finally
           OracleConn.Close()
       End Try

       Dim strSelect As String = String.Empty
       Dim objSelectCmd As New OracleCommand(strSelect, OracleConn)
       Dim P_Name1 As New OracleParameter
       Dim theSelectName As String = "Micky O'Shanahan"
       Dim strAnswer As String

       Try
           With objSelectCmd
               .CommandType = CommandType.Text
               .CommandText = "SELECT YerName FROM McTable WHERE YerName = :P_Name1"
               .Parameters.Add(P_Name1)
           End With
           With P_Name1
               .ParameterName = "P_Name1"
               .DbType = DbType.String
               .Direction = ParameterDirection.Input
               .Value = theSelectName
           End With

           OracleConn.Open()
           strAnswer = CType(objSelectCmd.ExecuteScalar(), String)
           OracleConn.Close()
           Console.WriteLine("This proves that " & strAnswer & " got in.")
       Catch ex As Exception
           Console.WriteLine(ex.Message)
       Finally
           OracleConn.Close()
       End Try
   End Sub

[VBAHole22]

Here is a good explanation. It outlines the threat of a SQL injection attack.

 

 

http://www.uberasp.net/getarticle.aspx?id=46

[samsmithnz]

You'd be even better if you used Stored Procedures. Then it would look something like this (connection is already created)

           'Create the command object
           objSQLCmd = New SqlCommand
           With objSQLCmd
               .Connection = objSQLConn ' the SQL connection
               .CommandType = CommandType.StoredProcedure
               .CommandText = "sp_GetEmployees"
               .Parameters.Add("@employee_name", SqlDbType.VarChar, 50)
               .Parameters.Item(0).Value = strEmployeeName
           End With

[mike55]

For the above scenario, books for the MySQL database tell users to replace ' with the symbols \'.

 

Mike

[VBAHole22]

And I just learned that SQL Server has an ESCAPE function that will do the same.

 

I like Stored Procs but when I am developing I find them annoying during development. Consider when you want to add an attribute. It's enough that you have to go through all of the .NET code to define the att then add it. But then you have to open an Oracle IDE and change the stored proc to match just to test something out.

 

I usually so with the text until the end then convert to a stored proc for performance. Although many folks say that if you send the same parameterized SQL query to one of the high end RDBMS systems it gets cached and assumes the effiecency of a stored proc.

[samsmithnz]

I honestly don't think its that big a deal to use Stored procedures during development. If I have to change a parameter or field, I only have to change the stored proc and the function calling it. Simple enough.