Insert values with single quotation in PostgreSQL

Question

I want to run the following query with a single quoted value.

INSERT INTO web_camp_keywords (web_id, keyword) VALUES (195, 'abc'hotels')

I just want to add abc'hotels value. I used backslash, but it did not work.

INSERT INTO web_camp_keywords (web_id, keyword) VALUES (195, 'abc\'hotels')

How can I resolve this?

try INSERT INTO web_camp_keywords (web_id, keyword) VALUES (195, 'abc''hotels') . but why not prepared statement? — Senthil Kumar
– Senthil Kumar, Commented Aug 8, 2012 at 5:20
tnx. I have not experence about prepared statement. I'll try that. — Dinuka Thilanga
– Dinuka Thilanga, Commented Aug 8, 2012 at 5:33
@Asurya then your application is FULL of what are called SQL injection security holes. Imagine what happens if the user who entered abc'hotels into a form instead entered abc'; DELETE FROM web_camp_keywords;-- or worse, abc';DROP SCHEMA public CASCADE;-- ? splat, there goes your entire database. See en.wikipedia.org/wiki/SQL_injection (Note: The DROP SCHEMA trick shouldn't work if your app connects with only the minimum required permissions - it shouldn't own the tables or be a superuser - but your app is probably the owner of the tables.) — Craig Ringer
– Craig Ringer, Commented Aug 8, 2012 at 6:36

Craig Ringer · Accepted Answer · 2012-08-08 06:42:14Z

8

You can escape the single quote with another single.

INSERT INTO web_camp_keywords (web_id, keyword) 
VALUES (195, 'abc''hotels')

But personally I think you should be using prepared statements with bind parameters.

Among other things, use of prepared statements with bind parameters is one of the easiest ways to help protect against SQL injection, the biggest source of security holes in web applications.

edited Aug 8, 2012 at 6:42

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Aug 8, 2012 at 5:22

Chris Moutray

18.4k7 gold badges49 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Masse · Accepted Answer · 2012-08-08 05:58:19Z

2

Like Chris Moutray and others mentioned, it would be best if you used pdo and prepared statements. Here is an example on how you could prepare a statement, provide the statement with values and then execute it. I left out the connection.

$statement = $pdo->prepare("insert into web_camp_keywords (web_id, keyword) values (:id, :keyword)");
$statement->bindValue(':id', 195);
$statement->bindValue(':keyword', "abc'hotels");
$statement->execute();

answered Aug 8, 2012 at 5:58

Masse

4,3743 gold badges32 silver badges42 bronze badges

Collectives™ on Stack Overflow

Insert values with single quotation in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related