CODEWITHSUNDEEP
sql
Meeeee
Meeeee

Reputation:

writing sql queries

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.

Upvotes: 1

Views: 268

Answers (4)

Christian13467
Christian13467

Reputation: 5604

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

Upvotes: 2

Martin B
Martin B

Reputation: 24140

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.

Upvotes: 3

Iain Hoult
Iain Hoult

Reputation: 4005

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'

Upvotes: 1

imnotneo
imnotneo

Reputation: 729

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

Upvotes: 1

Related Questions