Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

Avoid String Error (SQL)

If the user enters something like "Cats's" or anything with quotes, then the code below bombs when it is entered into the database. How can I change the code below to allow for quotes?

Dim g_iCurrentUserID as Integer
Dim F2 As String
Dim F3 As String
Dim F4 As String
Dim F5 As String
iCurrentUserID = 150
F2 = Me.cboBoxes.Text & vbNullString
F3 = Me.DTPicker1.Value
F4 = Trim(Format(Me.textBoxes(0).Text, gc_TimeFormat_24)) & vbNullString
F5 = Me.textBoxes(1).Text & vbNullString

s_Sql = "INSERT INTO tbl_Reminders (RemUserID, RemType, RemDate, RemTime, RemReminder) " _
& "Values ('" & g_iCurrentUserID & "', '" & F2 & "', '" & F3 & "', '" & F4 & "', '" & F5 & "');"
conn.Execute s_Sql

As always - Thanks

1 Solution
replace single quotes w/ double quotes ie:

F5 = Replace(Me.textBoxes(1).Text & vbNullString, "'", "''")
ADawn not only does that cause an error but it could be a security issue as well. I recommend *whenever* you create a SQL string you protect the variables. To show you what I mean. Imagine you let the user search for a user ID. Inside your program you would have your SQL string generated something like this:

sSQL = "SELECT * FROM Users WHERE UserID = " & txtUserID.Text

A normal user would simply put a number and find their record. However if someone were to put

543543; Drop Users

In the text box your sSQL string would end up being

SELECT * FROM Users WHERE UserID = 34234; Drop Users

Thereby dropping the entire users table!!!! To protect variables make two functions. iProtect and sProtect. sProtect should replace any single quotes with two single quotes and iProtect should multiply the parameter by one.

Hope this helps
Spencer Ruport
Mayank SAssociate Director - Product EngineeringCommented:
Use the data control:

Data1.DatabaseName = "...." ' what-ever it is
Data1.Database.Openrecordset ( "TABLE_NAME" )
With Data1.Recordset
  ![RemUserId] = g_iCurrentUserID ' if it is a number, the convert it first using the Val () function
  ![RemType] = F2
  ![RemDate] = F3
  ![RemTime] = F4
  ![RemReminder] = F5
End With

That'll do it, and I think that is the cleanest way to do it.

Thsi might be useful for you I use it when ever I am making an sql Inster Statement but it can be adapted to suit what ever your needs.


Option Explicit

Private SqlFields As String 'A string to hold the fields you are adding
Private SqlValues As String ' This will hold the values you are adding

Private Sub AddData(f As String, v As Variant)
    Select Case VarType(v)
        Case 0, 1, 13, 36, 8192 To 8228
            Exit Sub
        Case 8
            v = "'" & Replace(v, "'", "''") & "'"
        Case 7
            v = "#" & CDate(v) & "#"
    End Select
    If SqlValues = "" Then SqlValues = v Else SqlValues = SqlValues & ", " & v
    If SqlFields = "" Then SqlFields = f Else SqlFields = SqlFields & ", " & f
End Sub

If you were to make and Insert Statement then you woudl use this for your sql

strSQL = "INSERT INTO " & TableName & " (" & SqlFields & ") VALUES (" & SqlValues & ")"

Home this helps
ADawnAuthor Commented:
Interesting things from you all. Thanks


Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now