CODEWITHSUNDEEP
javasqlhibernate
Jaison
Jaison

Reputation: 81

How to escape " ' " in sql

Hi I am searching for a name combination in database. I am passing the combination as follows

"firstName='" + firstName + "'", "middleName='" + middleName + "'", "lastName='" + lastName + "'"

This works fine. But the problem comes where there are some " ' " in names how can i rectify it? eg: Johns' or Jerry's etc causes exception

Upvotes: 4

Views: 7244

Answers (7)

FThompson
FThompson

Reputation: 28697

You can ignore sinqle quotation characters (') in SQL by escaping them with a backslash \'.

Upvotes: 0

Tu Tran
Tu Tran

Reputation: 1987

If you're using Hibernate, you should use like this:

Query query = session.createQuery("from Something s where s.firstName = :firstName and s.middleName = :middleName and s.lastName = :lastName");
query.setString("firstName", firstName);
query.setString("middleName", middleName);
query.setString("lastName", lastName);

List<?> list = query.list();

Hope this can help you! You can see more at here and here

Upvotes: 3

swapy
swapy

Reputation: 1616

    String firstName="X";
    String middleName="Y";
    String lastName="Z";

 "firstName='" + firstName + "',middleName='" + middleName + "',lastName='" + lastName + "'";

You can use this to get output as

firstName='X',middleName='Y',lastName='Z'

Upvotes: 0

chaitu
chaitu

Reputation: 599

use preparedStatement it is easy for you

ps.executeUpdate("INSERT INTO tb_name values(?,?) WHERE id=?");
ps.setString(1,firstName);
ps.setString(2,middleName);
ps.setInt(3,id);
ps.execute();

Upvotes: 11

Herv&#233;
Herv&#233;

Reputation: 75

I guess the following also works (at least for MySQL):

SELECT login FROM usertable WHERE lastname="O'Neil";

Upvotes: -1

Ravi Jain
Ravi Jain

Reputation: 1482

Try this:

firstName = firstName.replace("'" , "''");

Use PreparedStatement instead , you would be better with that.

Upvotes: -1

sp00m
sp00m

Reputation: 48837

At least for MySQL, you have to put another ' before:

INSERT INTO table (column) VALUES ('this isn''t it');

Upvotes: 4

Related Questions