Serious problem with SQL database query (please help)

E

esposito

Centurion
Joined
Jul 11, 2003
Messages
103
Location
Perugia - Italy
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:

Visual Basic: 
          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.
 
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.
Visual Basic: 
          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()
 
Last edited:
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

Code: 
'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.
 
Dear VBAHole22, I managed to sort out my problem with a single line of code, which runs as follows:

Visual Basic: 
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.
 
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'
 
esposito said:
Dear VBAHole22, I managed to sort out my problem with a single line of code, which runs as follows:

Visual Basic: 
 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.
Click to expand...
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.
 
How would you transform my search function using parameters?

Joe Mamma said:
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.
Click to expand...
 
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:




Code: 
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
 
You'd be even better if you used Stored Procedures. Then it would look something like this (connection is already created)
Code: 
            '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
 
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.
 
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.
 
Back
Top