how to escape input but save unescaped into the database

Question

It is said that in order to prevent from SQL injection one should filter the input data eg. with addslashes or mysql_real_escape_string depending on used connection modules

However, data escaped with addslashes is being saved into the database WITH the slashes, so a user surname would save as O\'Reilly instead O'Reilly. The one needs to use stripslashes to display it correctly.

So how do I use addslashes and save into the database without slashes? Is it actually the way it should be done?

It looks like you escape string twice( maybe you are using PDO and `mysql_real_escape_string` at the same time). — a1ex07, Jul 08 '11 at 22:39
yes, I'm using PDO and I addslashes before. How exactly PDO escapes characters on its own? — vault-boy, Jul 08 '11 at 22:42
If you use PDO, and assign parameters with `bindParam`/`bindValue`, you don't need to worry about escaping, PDO will do it for you — a1ex07, Jul 08 '11 at 22:47
so, when using PDO basically, I could go with $_POST["var"] and pass it direcly into bindParam/bindValue, correct? And when using mysql_connect, I have to put $_POST through mysql_real_escape_string — vault-boy, Jul 08 '11 at 22:49

prodigitalson · Accepted Answer · 2011-07-08T22:45:35.707

6

You DONT use addslashes you use the appropriate DB specific escaping function like mysql_real_escape_string.

if you are using PDO then using a prepared statement will escape the variables as part of binding process. In this case all you need to do is something like:

$pdo = new PDO($dsn, $user, $name);
$stmt = $pdo->prepare('INSERT INTO your_table (col1, col2,col3) VALUES (?, ?, ?)');
$stmt->execute(array('value 1', 'value 2', 'value 3');

OR for extra readability and esier reuse you can use named params:

$pdo = new PDO($dsn, $user, $name);
$stmt = $pdo->prepare('INSERT INTO your_table (col1, col2,col3) VALUES (:col1, :col2, :col3)');
$stmt->execute(array(':col1' =>'value 1', ':col2' =>'value 2', ':col3' =>'value 3');

edited Jul 08 '11 at 22:45

answered Jul 08 '11 at 22:37

prodigitalson

60,050
10
100
114

ok... you guys contradict yourselves a bit... a1ex07 says bindParam escapes data, you say that prepare escapes data. – vault-boy Jul 08 '11 at 22:52
There are multiple ways to bind paramters to a statement... you can do it with bindParam, or you can do it as i have done here, or you can even use `PDO::quote` for instances where you cant use a prepared statement. There is more than one way to skin a cat but they will both do the same thing essentially. Which one you use depends what/how you need to code the functionality. [Check out this tutorial](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) for the basics. – prodigitalson Jul 08 '11 at 22:55

score 0 · Answer 2 · answered Jul 08 '11 at 22:42

addslashes is supposed to be a one-size-fits-all escaping mechanism. If you MySQL-escape an addslash-escaped strings, of course the value including the addslash slashes will be saved to the database. Use either or, not both.

Having said that, don't use addslashes. It serves no real purpose. Use the specific escaping mechanism for the appropriate situation. I.e., only use mysql_real_escape_string. Or prepared statements, which avoids the whole escaping mess to begin with.

score 0 · Answer 3 · answered Jul 08 '11 at 22:45

0

If you use prepared statements (via PDO or the mysqli library, for instance,) you don't need to escape or filter anything.

answered Jul 08 '11 at 22:45

dkretz

37,399
13
80
138

how to escape input but save unescaped into the database

3 Answers3

Linked