how to escape input but save unescaped into the database

It is said that in order to prevent from SQL injection one should filter the input data eg. with addslashes or mysql_real开发者_StackOverflow_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?

---

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');

---

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.

---

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