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

allowing use of apostrophe's in ASP form and SQL statement with variables

Hi there,
We have an ASP form and backend database / table with about 80 fields.
The user fills out the form and their entries become variables used in an SQL INSERT string like this snippet..

Then if surname = O'Reilly for example, we will get this error:
Syntax error (missing operator) in query expression

How can we update the code to allow apostrophes to be used without breaking our SQL string?
stringSQL = "INSERT INTO table (surname,firstname) VALUES ('" & request.form("txtSurname") & "','" & request.form("txtFirstName") & "')"

Open in new window

2 Solutions
try this
stringSQL = "INSERT INTO table (surname,firstname) VALUES ('" & replace(request.form("txtSurname"),"'","'") & "','" & replace(request.form("txtFirstName"),"'","'") & "')"

Open in new window

b0lsc0ttIT ManagerCommented:

You need to escape the apostrophe by placing another one in front of it.  E.g.

Replace(Request.Form("txtSurname"), "'", "''")

That will make it safe for an SQL statement or query.  Let me know if you have any questions or need more information.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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