Avoid String Error (SQL)

Posted on 2003-03-20
Medium Priority
Last Modified: 2010-05-01
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

Question by:ADawn
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 18

Accepted Solution

bobbit31 earned 100 total points
ID: 8174644
replace single quotes w/ double quotes ie:

F5 = Replace(Me.textBoxes(1).Text & vbNullString, "'", "''")
LVL 14

Expert Comment

ID: 8175055
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
LVL 30

Expert Comment

by:Mayank S
ID: 8179738
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.


Expert Comment

ID: 8180071
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

Author Comment

ID: 8186322
Interesting things from you all. Thanks


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 15 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question