CODEWITHSUNDEEP
phpmysqlpdo
Lelio Faieta
Lelio Faieta

Reputation: 6673

pdo prepare escaping single quotes

I use PDO in a web application I am building. I always thought (I am wrong actually) that using prepare should help with single quotes in the inserted variables but it seems that I miss something. I get an error inserting values like L'Aquila where there is a single quote in the input data.

My actual code is:

        $sql = "INSERT INTO anagrafiche SET
        id_ndg = '$protocol',
        nick = '$nick',
        nome = '$nome',
        cognome = '$cognome',
        ragsoc = '$ragsoc',
        leg_rappr = '$leg_rappr',
        cod_fisc = '$cod_fisc',
        p_iva = '$p_iva',
        cf_estero = '$cf_estero',
        SAE = '$sae',
        RAE = '$rae',
        ATECO = '$ateco',
        CRCODE = '$crcode',
        indirizzo = '$indirizzo',
        civico = '$civico',
        cap = '$cap',
        citta = '$citta',
        prov = '$prov',
        tel = '$tel',
        cell = '$cellulare',
        mail = '$mail',
        note = '$note',
        file_ci = '$file_ci',
        file_cf = '$file_cf',
        file_visura = '$file_visura',
        cittadinanza = '$cittadinanza',
        res_fiscale = '$res_fiscale',
        is_curatore = '$is_curatore',
        is_legale = '$is_legale',
        is_tribunale = '$is_tribunale',
        is_fornitore = '$is_fornitore' ";
    try{
        $s = $pdo->prepare($sql);               
        $s->execute();
    }
    catch (PDOException $e){
        $error = 'Errori nel caricamento: '.$e->getMessage();
    }

and when I try to load a string containing the single quote I get an error like this while trying to load the string Piazza d'Armi :

Errori nel caricamento: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Armi', civico = '0', cap = '83100', citta = 'Avellino', prov' at line 15

What am I missing? I don't think that PDO quote can do the job for me but maybe it's me that I don't get the point

Upvotes: 4

Views: 11872

Answers (1)

Mike Brant
Mike Brant

Reputation: 71384

It helps with single quotes only if you do parametrized prepared statements, otherwise all you are doing is string concatenation and have to be subject to properly forming your SQL.

Try something like:

$sql = "INSERT INTO anagrafiche SET
        id_ndg = :protocol,
        nick = :nick,
        nome = :nome,
        ...
        ";
$params = array(
    ':protocol' => $protocol,
    ':nick' => $nick,
    ':nome' => $nome,
    ...
); 
try{
    $s = $pdo->prepare($sql);               
    $s->execute($params);
} catch (PDOException $e) {
    ...
}

This also gives you the added advantage of mitigating SQL injection attacks.

If you want to go a step further and enforce data types, you could use bindValue() or bindParam()

like:

$sql = "INSERT INTO anagrafiche SET
        id_ndg = :protocol,
        nick = :nick,
        nome = :nome,
        ...
        "; 
try{
    $s = $pdo->prepare($sql);
    $s->bindParam(':protocol', $protocol, PDO::PARAM_ST);
    $s->bindParam(':nick', $nick, PDO::PARAM_ST);
    $s->bindParam(':nome', $nome, PDO::PARAM_ST);
    ...
    $s->bindParam(':some_integer', $some_integer, PDO::PARAM_INT);
    ...           
    $s->execute();
} catch (PDOException $e) {
    ...
}

bindValue() has similar syntax to bindParam() but only binds the value of the variable at the time of binding to the parameter rather than the value of the variable at the time of statement execution.

Upvotes: 17

Related Questions