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?

but In mysql database I get: <script>alert('heheheheheheh')</script> so when user need this data then script been runned — MonkeyBusiness
– MonkeyBusiness, Commented Jun 1, 2015 at 14:00
@MonkeyBusiness: this issue isn't about storing values in MySQL. There's no problem storing that string as a value in a column. When you pull that value out of the database, you probably want to run that value through a function like htmlentities as you output that to a web page; just like your're already doing on all potentially "unsafe" values that you are putting on your webpage. — spencer7593
– spencer7593, Commented Jun 1, 2015 at 14:15
@Uchiha PDO itself doesn't sanitize values. Only the use of Prepared Statements makes it safe. — Charlotte Dunois
– Charlotte Dunois, Commented Jun 1, 2015 at 14:20
@MonkeyBusiness: Your application should treat "stored values" as potentially unsafe, just like it does values from $_GET and $_POST. The OWASP project makes a note if this, under "Protecting from stored attacks" https://www.owasp.org/index.php/Interpreter_Injection. As Charlotte noted, PDO doesn't "sanitize" values provided in SQL statements. Using prepared statements with bind placeholders is a mechanism that effectively closes the door on a lot of SQL Injection vectors. — spencer7593
– spencer7593, Commented Jun 1, 2015 at 14:27

spencer7593 · Accepted Answer · 2015-06-04 04:45:11Z

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

&lt;script&gt;alert('heheheheheheh')&lt;/script&gt;

(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.

Bitwise Creative · Accepted Answer · 2015-06-04 04:30:18Z

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

Ferrakkem Bhuiyan · Accepted Answer · 2015-06-04 04:38:03Z

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();
}

Collectives™ on Stack Overflow

How to safely insert data to mysql using php pdo

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related