PDO Not escaping string quotes

Question

I have a problem in a php script where I am using PDO to access my database. I can't seem to get PDO to escape my string when I use prepare()or execute()in PDO. I've looked all over and I haven't found an answer to this problem because everywhere I look it says PDO automatically escapes the strings. Here's my code :

$statement = $db->prepare("INSERT INTO Table (ID, Column1, Column2) VALUES (NULL, '$var1', '$var2')");
$query->execute();

Let's admit $var1 = "abc'def" and $var2 = "123" The problem is I get an error message because the quote wasn't escaped.

Error : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'def', '123')' at line 1

I've also tried using the query() method but same prpblem with the quotes. I really don't understand and it's frustrating. Thanks for any help.

Check out the [examples](http://ca.php.net/pdo.prepare). – netcoder Mar 12 '12 at 20:36 — netcoder, Mar 12 '12 at 20:36

score 13 · Accepted Answer · answered Mar 12 '12 at 20:35

13

Try this:

# Took out ID, as it should be auto_increment and handled by database
$statement = $db->prepare("INSERT INTO Table (Column1, Column2) VALUES (:col1, :col2)");

$statement->bindValue(':col1', $var1, PDO::PARAM_STR);
$statement->bindValue(':col2', $var2, PDO::PARAM_INT);

$statement->execute();

answered Mar 12 '12 at 20:35

Mike Purcell

19,847
10
52
89

No problem. For future reference, read the PHP docs, 95% of the time there are great examples from which glean how to implement the api calls correctly. Accept when you can. – Mike Purcell Mar 12 '12 at 20:46

score 2 · Answer 2 · answered Mar 12 '12 at 20:38

2

Please take a look at the bindParam() method of the PDO library.

This makes your query look like this:

$statement = $db->prepare("INSERT INTO Table (ID, Column1, Column2) VALUES (NULL, ?, ?)");
$statement->bindParam(1, $var1, PDO::PARAM_STR);
$statement->bindParam(2, $var2, PDO::PARAM_INT);

$query->execute();

//I really should load the new answers :) When the bar says there are.

answered Mar 12 '12 at 20:38

stUrb

6,612
8
43
71

As Mike Purcell suggested it I did it with a similar method called `bindValue()`I prefer keys rather than indexes for objects in a string. – califrench Mar 12 '12 at 21:07
1

This approach will work the same, the only downfall is if you have a query with a high number of params, it can get confusing. – Mike Purcell Mar 13 '12 at 21:20

score 0 · Answer 3 · answered Mar 12 '12 at 20:39

0

There is a function for that purpose. Have a look at www.php.net/addslashes

answered Mar 12 '12 at 20:39

Kasper Munck

4,173
2
27
50

3

Why should you use addslashes when the PDO extension has built in functions for that? – stUrb Mar 12 '12 at 20:41
You probably shouldn't. Wasnt't aware PDO has the ability do solve such thing. – Kasper Munck Mar 12 '12 at 21:05

PDO Not escaping string quotes

3 Answers3

Linked