locked
Saved Parameter Query to prevent SQL injection RRS feed

  • Question

  • Hi all

    I have a problem to adding information into a table in Access. In my main form i got some fields to introduce information into the table. when i click on SaveButton this information will be save. The problem i have is: in text field, if i use " ' " the insert into query gets an error and can't save that information.

    In this example i declare the value from the main form objects, i don't want use: '" & param & "' i need to know how to declare a Paremeter query to prevent SQL injection

    "INSERT INTO WorkTask([Descripción],[Tarea],[Sub-Tarea],[Cliente],[Proyecto],[Tiempo Dedicado],[Fecha],[Descripcion_Ad])" & " VALUES( " & Descripcion.Value & "," & Tarea.Value & ", " & SubTarea.Value & " , " & Cliente.Value & ", " & Proyecto.Value & ", " & Tiempo_Dedicado.Value & "," & Fecha.Value & ", " & Descripcion_Ad.Value & "

    Thanks in advance

     

    Monday, February 6, 2012 10:36 AM

Answers

  • Thanks you for you're answers, i have fix the problem, to do it i use the code below

    Dim Qry As DAO.QueryDef

    Set Qry = CurrentDb.CreateQueryDef("", "INSERT INTO WorkTask([Descripción],[Tarea],[Sub-Tarea],[Cliente],[Proyecto],[Tiempo Dedicado],[Fecha],[Descripcion_Ad])" & _
           " VALUES([Description], [Task], [SubTask], [Client], [Project], [Time_Assign], [Date], [Second_Descrip])")
         
           Qry.Parameters("[Description]") = Descripcion.Value
           Qry.Parameters("[Task]") = Tarea.Value
           Qry.Parameters("[SubTask]") = SubTarea.Value
           Qry.Parameters("[Client]") = Cliente.Value
           Qry.Parameters("[Project]") = Proyecto.Value
           Qry.Parameters("[Time_Assign]") = Tiempo_Dedicado.Value
           Qry.Parameters("[Date]") = Fecha.Value
           Qry.Parameters("[Second_Descrip]") = Descripcion_Ad.Value
          
           Qry.Execute

    I was looking for something that would parameterize the query to insert data, it was what i wanted. I apologize if the question has not been clear.
    My written English is somewhat limited.


    But anyway thank you very much

    • Marked as answer by Joanmi Monday, February 6, 2012 1:57 PM
    Monday, February 6, 2012 1:56 PM
  • There should be no problem inserting a value containing an apostrophe (single quote character).  The following works to insert my own name in its original Irish form into a table for instance:

    INSERT INTO Contacts(FirstName,LastName)
    VALUES("Cináed", "O'Siridean");

    If you need to insert a double quote character represent it by a contiguous pair of double quotes characters, e.g the following would also work:

    INSERT INTO Contacts(FirstName,LastName)
    VALUES("Cináed", "O""Siridean");

    making my name:

    Cináed O"Siridean

    I think your problem is that you are not delimiting values of text data type with double quotes.  Assuming the Descripcion column is of text data type the expression to build the SQL statement would be:

    "INSERT INTO WorkTask([Descripción],[Tarea],[Sub-Tarea],[Cliente],[Proyecto],[Tiempo Dedicado],[Fecha],[Descripcion_Ad])" & " VALUES( """ & Descripcion.Value & """," & Tarea.Value & ", " & SubTarea.Value & " , " & Cliente.Value & ", " & Proyecto.Value & ", " & Tiempo_Dedicado.Value & "," & Fecha.Value & ", " & Descripcion_Ad.Value & ")"

    The same should be done with other text values.  Note also that the expression needs a closing parenthesis.

    Ken Sheridan, Stafford, England
    • Marked as answer by Joanmi Monday, February 6, 2012 1:57 PM
    Monday, February 6, 2012 12:31 PM
  • The point about SQL injection is, what happens if (for example) SubTarea.Value contains:

    ";deleteharddisc();
    

    Or something like that? Your user-supplied values might contain double quotation marks. Or any other sort of delimiter you choose. Someone might give their child a name with a double-quotation-mark in it, just to cause problems for SQL engineers and make the school administrators curse...

    So someone with expertise in parameterised queries needs to come here and give some advice. Otherwise, you might consider using an old-school SQL technique: escaped character sequences... Something like this... Dump the following code into a VBA module called "SQL":

    Private Const date_min As Date = #1/1/1753#
    Private Const date_max As Date = #12/31/9999#
    Public Function Ecd(ByVal value_to_encode As Variant) As String
    ' Ecd i.e., ENCODE.
    ' Take VBA variables and convert them into something the current dialect of SQL will understand.
        If IsNull(value_to_encode) Or IsEmpty(value_to_encode) Then
            Ecd = "Null" ' Should work for INSERTs.  In testing for WHERE CONDITIONS though, need "Is Null", not "=Null"
            Exit Function
        End If
        Select Case VarType(value_to_encode)
            Case vbBoolean:
                Ecd = IIf(value_to_encode, "TRUE", "FALSE")
            Case vbByte, vbInteger, vbLong, vbSingle, vbDouble:
                Ecd = CStr(value_to_encode)
            Case vbDate:
                If CDate(value_to_encode) < date_min Or CDate(value_to_encode) > date_max Then
                     Err.Raise vbObjectError + 1, "SQL.Encode", "Date outside range permitted by SQL dialect in use."
                End If
                Ecd = "#" & SQL.FormatTime(CDate(value_to_encode)) & "#"
            Case vbString:
                Ecd = "'" & SQL.EncodeString(value_to_encode) & "'"
        End Select
    End Function
    Public Function EncodeString(ByVal sql_string As String)
        sql_string = Strings.Replace(sql_string, "'", "''")
        EncodeString = sql_string
    End Function
    Public Function FormatTime(ByRef date_val As Date)
        FormatTime = Format(date_val, "yyyy-mm-dd hh:nn:ss")
    End Function
    

    Now, write your SQL query like this, in VBA:

    "INSERT INTO WorkTask([Descripción],[Tarea],[Sub-Tarea],[Cliente],[Proyecto],[Tiempo Dedicado],[Fecha],[Descripcion_Ad])" & " VALUES( " & SQL.Ecd(Descripcion.Value) & "," & SQL.Ecd(Tarea.Value) & ", " & SQL.Ecd(SubTarea.Value) & " , " & SQL.Ecd(Cliente.Value) & ", " & SQL.Ecd(Proyecto.Value) & ", " & SQL.Ecd(Tiempo_Dedicado.Value) & "," & SQL.Ecd(Fecha.Value) & ", " & SQL.Ecd(Descripcion_Ad.Value) & ";"
    

    This means:

    • Your query is now immune from SQL injection (any challenge to this assertion?)
    • Your code is highly maintainable - if, for example, you switch your database back-end to a different SQL engine / dialect, you just need to change the delimiters in the SQL.Ecd function - you don't need to rewrite all the queries in your database.
    • Your code is very simple to understand, for anyone who understands elementary SQL. No parameters. It's all in one place.

    Matthew Slyman M.A. (Camb.)
    • Edited by Matthew Slyman Monday, February 6, 2012 1:09 PM
    • Marked as answer by Joanmi Monday, February 6, 2012 1:57 PM
    Monday, February 6, 2012 1:04 PM

All replies

  • There should be no problem inserting a value containing an apostrophe (single quote character).  The following works to insert my own name in its original Irish form into a table for instance:

    INSERT INTO Contacts(FirstName,LastName)
    VALUES("Cináed", "O'Siridean");

    If you need to insert a double quote character represent it by a contiguous pair of double quotes characters, e.g the following would also work:

    INSERT INTO Contacts(FirstName,LastName)
    VALUES("Cináed", "O""Siridean");

    making my name:

    Cináed O"Siridean

    I think your problem is that you are not delimiting values of text data type with double quotes.  Assuming the Descripcion column is of text data type the expression to build the SQL statement would be:

    "INSERT INTO WorkTask([Descripción],[Tarea],[Sub-Tarea],[Cliente],[Proyecto],[Tiempo Dedicado],[Fecha],[Descripcion_Ad])" & " VALUES( """ & Descripcion.Value & """," & Tarea.Value & ", " & SubTarea.Value & " , " & Cliente.Value & ", " & Proyecto.Value & ", " & Tiempo_Dedicado.Value & "," & Fecha.Value & ", " & Descripcion_Ad.Value & ")"

    The same should be done with other text values.  Note also that the expression needs a closing parenthesis.

    Ken Sheridan, Stafford, England
    • Marked as answer by Joanmi Monday, February 6, 2012 1:57 PM
    Monday, February 6, 2012 12:31 PM
  • The point about SQL injection is, what happens if (for example) SubTarea.Value contains:

    ";deleteharddisc();
    

    Or something like that? Your user-supplied values might contain double quotation marks. Or any other sort of delimiter you choose. Someone might give their child a name with a double-quotation-mark in it, just to cause problems for SQL engineers and make the school administrators curse...

    So someone with expertise in parameterised queries needs to come here and give some advice. Otherwise, you might consider using an old-school SQL technique: escaped character sequences... Something like this... Dump the following code into a VBA module called "SQL":

    Private Const date_min As Date = #1/1/1753#
    Private Const date_max As Date = #12/31/9999#
    Public Function Ecd(ByVal value_to_encode As Variant) As String
    ' Ecd i.e., ENCODE.
    ' Take VBA variables and convert them into something the current dialect of SQL will understand.
        If IsNull(value_to_encode) Or IsEmpty(value_to_encode) Then
            Ecd = "Null" ' Should work for INSERTs.  In testing for WHERE CONDITIONS though, need "Is Null", not "=Null"
            Exit Function
        End If
        Select Case VarType(value_to_encode)
            Case vbBoolean:
                Ecd = IIf(value_to_encode, "TRUE", "FALSE")
            Case vbByte, vbInteger, vbLong, vbSingle, vbDouble:
                Ecd = CStr(value_to_encode)
            Case vbDate:
                If CDate(value_to_encode) < date_min Or CDate(value_to_encode) > date_max Then
                     Err.Raise vbObjectError + 1, "SQL.Encode", "Date outside range permitted by SQL dialect in use."
                End If
                Ecd = "#" & SQL.FormatTime(CDate(value_to_encode)) & "#"
            Case vbString:
                Ecd = "'" & SQL.EncodeString(value_to_encode) & "'"
        End Select
    End Function
    Public Function EncodeString(ByVal sql_string As String)
        sql_string = Strings.Replace(sql_string, "'", "''")
        EncodeString = sql_string
    End Function
    Public Function FormatTime(ByRef date_val As Date)
        FormatTime = Format(date_val, "yyyy-mm-dd hh:nn:ss")
    End Function
    

    Now, write your SQL query like this, in VBA:

    "INSERT INTO WorkTask([Descripción],[Tarea],[Sub-Tarea],[Cliente],[Proyecto],[Tiempo Dedicado],[Fecha],[Descripcion_Ad])" & " VALUES( " & SQL.Ecd(Descripcion.Value) & "," & SQL.Ecd(Tarea.Value) & ", " & SQL.Ecd(SubTarea.Value) & " , " & SQL.Ecd(Cliente.Value) & ", " & SQL.Ecd(Proyecto.Value) & ", " & SQL.Ecd(Tiempo_Dedicado.Value) & "," & SQL.Ecd(Fecha.Value) & ", " & SQL.Ecd(Descripcion_Ad.Value) & ";"
    

    This means:

    • Your query is now immune from SQL injection (any challenge to this assertion?)
    • Your code is highly maintainable - if, for example, you switch your database back-end to a different SQL engine / dialect, you just need to change the delimiters in the SQL.Ecd function - you don't need to rewrite all the queries in your database.
    • Your code is very simple to understand, for anyone who understands elementary SQL. No parameters. It's all in one place.

    Matthew Slyman M.A. (Camb.)
    • Edited by Matthew Slyman Monday, February 6, 2012 1:09 PM
    • Marked as answer by Joanmi Monday, February 6, 2012 1:57 PM
    Monday, February 6, 2012 1:04 PM
  • Thanks you for you're answers, i have fix the problem, to do it i use the code below

    Dim Qry As DAO.QueryDef

    Set Qry = CurrentDb.CreateQueryDef("", "INSERT INTO WorkTask([Descripción],[Tarea],[Sub-Tarea],[Cliente],[Proyecto],[Tiempo Dedicado],[Fecha],[Descripcion_Ad])" & _
           " VALUES([Description], [Task], [SubTask], [Client], [Project], [Time_Assign], [Date], [Second_Descrip])")
         
           Qry.Parameters("[Description]") = Descripcion.Value
           Qry.Parameters("[Task]") = Tarea.Value
           Qry.Parameters("[SubTask]") = SubTarea.Value
           Qry.Parameters("[Client]") = Cliente.Value
           Qry.Parameters("[Project]") = Proyecto.Value
           Qry.Parameters("[Time_Assign]") = Tiempo_Dedicado.Value
           Qry.Parameters("[Date]") = Fecha.Value
           Qry.Parameters("[Second_Descrip]") = Descripcion_Ad.Value
          
           Qry.Execute

    I was looking for something that would parameterize the query to insert data, it was what i wanted. I apologize if the question has not been clear.
    My written English is somewhat limited.


    But anyway thank you very much

    • Marked as answer by Joanmi Monday, February 6, 2012 1:57 PM
    Monday, February 6, 2012 1:56 PM
  • Your written English is fine - I have no problems understanding your intended meanings here.

    Thank you for sharing your parameterized-SQL-insert solution!


    Matthew Slyman M.A. (Camb.)
    Monday, February 6, 2012 2:34 PM