[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

apostrophes in SQL

I am writing a sql statement and using ADO to execute it through a connection....the problem is that if a user enters an apostrophe (like in O'reilly) or something, it screws up the SQL statement.  How do I avoid this?
0
jedimike
Asked:
jedimike
  • 2
  • 2
1 Solution
 
SynstheCommented:
You have to escape the apostrophe, like so -->  \'

You can either enter this directly into statement itself, or if your statement is built based on outside input (via a form, etc) you should use a regular expression statement to search for any apostrophe's (such as that one) and replace them with the escaped version (\').
0
 
jedimikeAuthor Commented:
so in other words...
this:
insert into table(name) VALUES('Mike O'Reilly);

becomes this:
insert into table(name) VALUES('Mike O\'Reilly);
0
 
jedimikeAuthor Commented:
forgot the ' at the end:...
insert into table(name) VALUES('Mike O\'Reilly');
0
 
SynstheCommented:
That's exactly what I meant, yup. Give it a try. :)
0
 
adinasCommented:
Just Use the Replace function.

MyText = 'Mike O'Reilly'
Replace = Replace(MyText ,"'", "\'")

PS the HTML value for ' is &#39
So you might want to use:
Replace(MyText ,"'", "&#39")
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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