SQL to Query text in access with an apostrophe in it

Question

I am trying to query a name (Daniel O'Neal) in column names tblStudents in an Access database, however Access reports a syntax error with the statement:

Select * from tblStudents where name like 'Daniel O'Neal'

due to the apostrophe in the name.

How do I overcome this?

Tuneberg

Answer 1

You escape ' by doubling it, so:

Select * from tblStudents where name like 'Daniel O''Neal' 

Note that if you're accepting "Daniel O'Neal" from user input, the broken quotation is a serious security issue. You should always sanitize the string or use parametrized queries.

Anthesis

Out of curiosity Alex why would the escaping of quotes cause a security issue ? – Vichy

@RichardSpencer in certain scenarios if you pass around string variables containing unescaped ' characters and then use them to construct SQL statements your open to SQL Injection attacks; en.wikipedia.org/wiki/SQL_injection – Anthesis

Answer 2

When you include a string literal in a query, you can enclose the string in either single or double quotes; Access' database engine will accept either. So double quotes will avoid the problem with a string which contains a single quote.

SELECT * FROM tblStudents WHERE [name] Like "Daniel O'Neal";

If you want to keep the single quotes around your string, you can double up the single quote within it, as mentioned in other answers.

SELECT * FROM tblStudents WHERE [name] Like 'Daniel O''Neal';

Notice the square brackets surrounding name. I used the brackets to lessen the chance of confusing the database engine because name is a reserved word.

It's not clear why you're using the Like comparison in your query. Based on what you've shown, this should work instead.

SELECT * FROM tblStudents WHERE [name] = "Daniel O'Neal";

Laski

Answer 3

Escape the apostrophe in O'Neal by writing O''Neal (two apostrophes).

Nisse

Answer 4

...better is declare the name as varible ,and ask before if thereis a apostrophe in the string:

e.g.:

DIM YourName string

YourName = "Daniel O'Neal"

  If InStr(YourName, "'") Then
      SELECT * FROM tblStudents WHERE [name]  Like """ Your Name """ ;
   else
      SELECT * FROM tblStudents WHERE [name] Like '" Your Name "' ;       
  endif

Deannadeanne

And if my name is Daniel"O'Neal, then what? – Venetic

Answer 5

How about more simply: Select * from tblStudents where [name] = replace(YourName,"'","''")

Utile

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited short-term help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made. – Workmanship

Answer 6

I was looking for how to find all records where there was an apostrophe in the column value. Using the double ' suggested by @Alex K, I came up with

SELECT * FROM [table] WHERE ([column] LIKE '%''%')

Adding it to this question for others who maybe looking for the same resolution.

Contrabassoon

That is good for Access' ANSI-92 mode, but if you're running the query in ANSI-89 mode, you need * instead of % as the wildcard. – Laski