PDO : Need to escape string or not ?

Question

I use this code to insert some data into my database. I adapt my previous code based on mysqli to use PDO now.

For the 2 parameters name and id, do i need to escape them using a function like mysqli_real_escape_string with PDO ? or is it OK to pass these params direclty in the query ?

<?php
try
{  
    $pdo = new   PDO('mysql:host='.$servername.';port='.$dbport.';dbname='.$dbname.'', $username, $decodedPwd);

    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $json = $_POST['jsonData'];
    $id = $json["id"]
    $name = $json["name"]

    $pdo->beginTransaction();

    // do request

    $pdo->query('INSERT INTO test(id, name) VALUES ('$id', '$name')');

    $pdo->commit();

    echo 'Everything is OK';
}
catch(Exception $e)
{
    $pdo->rollback();

    echo 'An error occurred :<br />';
    echo 'Error : '.$e->getMessage().'<br />';
    echo 'N° : '.$e->getCode();

    exit();
}

You should use prepared statements and use placeholders instead of injecting the vars yourself — DarkBee, May 25 '16 at 09:42
PDO is a standalone solution, it doesn't need mysqli to work. — Álvaro González, May 25 '16 at 09:42
1) That code isn't even valid. 2) No, don't concatenate variables into the query, in particular from user input, you end up with SQL injection vulns 3) Use prepared statements. — Jonnix, May 25 '16 at 09:42
what is the PDO equivalent of mysql_real_escape_string - http://stackoverflow.com/questions/14012642/what-is-the-pdo-equivalent-of-mysql-real-escape-string — Krish R, May 25 '16 at 09:44
None, PDO escapes the string for you without having to call a special function. — s1h4d0w, May 25 '16 at 10:31

score 4 · Accepted Answer · answered May 25 '16 at 09:43

4

You need to prepare your statement, try this:

$query = $pdo->prepare('INSERT INTO test(id, name) VALUES (:theid, :thename)');
$query->execute(array(
    'theid' => $id,
    'thename' => $name
));

answered May 25 '16 at 09:43

s1h4d0w

762
6
27

Ok. Using this solution no need to escape input string? – wawanopoulos May 25 '16 at 10:04
Correct, no need to escape string input. That's the purpose of prepared statements. They "prepare" your variables so that they can do no harm. – s1h4d0w May 25 '16 at 10:29
Prepared Statements are not 100% safe against sqlinjections! [read this](https://phpdelusions.net/pdo/sql_injection_example) – aProgger Oct 04 '21 at 16:37

Your Common Sense · Answer 2 · 2016-05-25T10:09:36.453

You don't have to escape strings but you have to use preared statements.

Here is what your code should be.

<?php
$pdo = new PDO('mysql:host='.$servername.';port='.$dbport.';dbname='.$dbname.'', $username, $decodedPwd);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$json = $_POST['jsonData'];

$pdo->prepare('INSERT INTO test(id, name) VALUES (:id,:name)')->execute($json);
echo 'Everything is OK';

note that a transaction is useless for just a single query and the way you are reporting errors is wrong.

also, if $json already contains the all the data for thequery, no need to store its contents in separate variables.

score 0 · Answer 3 · answered May 25 '16 at 09:42

0

It's not okay. You need to use prepared statements or PDO::quote().

answered May 25 '16 at 09:42

Shira

6,392
2
25
27

PDO : Need to escape string or not ?

3 Answers3