How to safely insert data to mysql using php pdo

Question

I have this php pdo:

try {        
                $STH = $db->prepare("INSERT INTO racuni (napomene) VALUES (:12)");

                $STH->bindParam(':12', $_POST['napomena'], PDO::PARAM_STR);



            } catch (PDOException $e) {
                echo $e->getMessage();
            }

and now when I try to add this data to mysql:

<script>alert('heheheheheheh')</script>

there is no string escapes? And in database I have the same data:<script>alert('heheheheheheh')</script>

SO,

What is the best, safe way to insert data to mysql using php pdo?

Answer 1

When retrieving values from the database, you need to treat those values as if they are unsafe, as if they might include HTML special characters, or might contain javascript.

To get that value displayed safely in a web page, you need to run that value through a proper escaping mechanism.

PHP provides the htmlentities function to do just that, replacing HTML special characters with suitable replacements. As an example:

$val = htmlentitites("<script>alert('heheheheheheh')</script>");

Would assign something like this to $val

<script>alert('heheheheheheh')</script>

(It might also replace some other characters.) But the end result is if you put that string out on a web page, what you are going to "see" displayed on the web page appears like the original string. That string won't be interpreted as javascript.

Bottom line, you can't assume that because a string is being returned from the database that it's "safe". You must treat it as potentially unsafe.

---

Does PDO "sanitize" input?

In a word, NO. PDO doesn't sanitize values in SQL statements to remove HTML special characters or other potentially unsafe values.

What the prepared statement with the bind placeholder does is ensure that the value supplied in the statement gets passed into the database, without being interpreted as SQL text.

As an example, using PDO prepared statement with bind placeholder

$sql='INSERT INTO tab (col) VALUES (:val)'; 
$sth=$db->prepare($sql);
$sth->bindParam(':val', $value);
$sth->execute();

vs. incorporating the value into the SQL statement

$sql = "INSERT INTO tab (col) VALUES ('" ,. $value ."')";
$db->query($sql);

Consider what happens in each case when $value contains this string

foo'); DROP TABLE tab; --

With the first pattern (prepared statement with bind placeholder), that string value gets passed to the database, and stored in the column.

In the second example, incorporating that value into the text of the SQL statement, we get potentially dangerous SQL statements submitted:

INSERT INTO tab (col) VALUES ('foo'); DROP TABLE tab; --')

This an example of what SQL Injection vulnerability is about. And this demonstrates why using prepared statements with bind placeholders thwarts SQL Injection, it defends against a whole swath of nastiness that can happen when we don't treat values as potentially unsafe.

---

If $value contains the string:

<script>alert('heheheheheheh')</script>

With the prepared statement and bind placeholder, that's the value that's going to be stored in the database. It's just a string. It won't be interpreted as part of the SQL statement.

We can get the same thing to happen with the other pattern, that's vulnerable to SQL Injection, if we use double quotes instead of single quotes around the string literal in our SQL, e.g.

INSERT INTO tab (col) VALUES ("<script>alert('heheheheheheh')</script>")

Or, if we used an "escape string" function

INSERT INTO tab (col) VALUES ('<script>alert(''heheheheheheh'')</script>')

Again, that string gets stored in the database, because it's a valid string. It doesn't matter one whit whether that's got HTML special characters in it.

---

Bottom line, PDO does not sanitize HTML characters in strings. Your code needs to handle all values returned from the database as if they are potentially unsafe, and run them through the htmlentities or a similar function to "disarm" the values from being interpreted e.g. as javascript.

Answer 2

Using prepared statements is best, which is what you're doing (assuming you're executing the statement (not in your code sample)). Prepared statements escape values to prevent SQL injection, not cross-site scripting.

The value is unchanged entering the database, which is good (IMO). You should filter the values when you output them if needed (for example, htmlentities()).

You can also bind params in the execute method:

$STH = $db->prepare("INSERT INTO racuni (napomene) VALUES (?)");
$STH->execute(array($_POST['napomena']));

Answer 3

First you need to tell PDO to throw exceptions as bellow:

$pdo = new PDO("mysql:host={$dbhost};dbname={$dbname}", $dbuser, $dbpass);
// add this:
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Now wrap your database operations in a try - catch block:

try
{
  $statement = $pdo->prepare("INSERT INTO racuni (napomene) VALUES (:12)");

  $statement->bindParam(':12', $_POST['napomena'], PDO::PARAM_STR);
  // etc.

  $statement->execute();
}
catch ( PDOException $exception )
{
    echo "PDO error :" . $exception->getMessage();
}