writing sql queries

Question

Hello.

I am trying to write an sql query and I am having a problem. When we want to write a query with a where clause to narrow down our results, we can do

... where name = 'John'

(Where name is a column in the table). Now I am trying to insert a clause like this except the name is "O'Malley". So I thought the query would be

... where name = 'O'Malley'

but this gives me a null pointer exception. Does anyone know how you could solve this problem?

Thanks for your help in advance.

Answer 1

Depending on the database you could escape the ' I think. Have a look at http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

Answer 2

Your problem is that the single quote in the string "O'Malley" is interpreted by SQL as the string terminator. To escape a single quote, replace it with two single quotes, like this:

where name = 'O''Malley'

Edit: If the string "O'Malley" came from a user input, your code is vulnerable to an SQL injection exploit. To avoid this risk, use a parameterized query.

Answer 3

If you use two apostrophes together in you search string SQL will realise that it is part of the string and isn't part of the SQL syntax.

where name = 'O''Malley'

Answer 4

Thanks Iain and Martin. Your soloution worked fine for me. Thanks again.

where name = 'O''Malley'

Answer 5

Use bind variables to avoid thinking about quotation problems. Bind variables beware of sql injection to.