Pdo prepares to release single quotes
I am using PDO in a web application I am building. I have always thought (I am wrong actually) that using prepare
should help with single quotes in inserted variables, but it seems that I am missing something. I am getting an error inserting values like L'Aquila where there is one quote in the input.
My actual code:
$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 that contains a single quote, I get an error like this when trying to load the Piazza d'Armi string
Errori nel caricamento: SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual which matches your MySQL server version for correct syntax use near "armie", civico = '0', cap = '83100', citta = 'Avellino', prov 'on line 15
What am I missing? I don't think the PDO quote can do the job for me, but maybe it's me that I don't understand the point
source to share
It only helps with single quotes if you are executing parameterized prepared statements, otherwise all you do is string concatenation and should be subject to your SQL shaping correctly.
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) {
...
}
It also gives you the added benefit of reducing SQL injection attacks.
If you want to go one step further and apply data types, you can use bindValue()
orbindParam()
as:
$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 for bindParam()
, but only binds the value of the variable at the time of binding to the parameter, not the value of the variable at the time of the statement.
source to share