SQL request variable quoting

Question

My MySQL query won't work because of quotes, or missing quotes, but I don't understand how to use it properly. I need some explanations about this:

Perl script writing to .csv file:

open(ECRIRE,">$ARGV[1]") || die ("Impossible de creer le fichier de sortie");
foreach my $key (sort keys %caisse)
{
 print ECRIRE "insert into etablissement(code_etablissement,nom, contact_ce_nom, contact_ce_tel, contact_ce_mail) values ($key,$caisse{$key}[0];$caisse{$key}[1];$caisse{$key}[2];$caisse{$key}[3]) on duplicate key update contact_ce_nom=$caisse{$key}[1],contact_ce_tel=$caisse{$key}[2],contact_ce_mail=$caisse{$key}[3];\n";
}
close(ECRIRE);

Bash script executing the SQL request:

$mysql -f -h $db_address -P $db_port -u $db_user -p$db_passwd $db_name < $vtiger_temporary_file_etablissement_clean

Mysql is crying over almost every informations like this one

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 '[email protected]) on duplicate key update contact_ce_no' at line 1

I even tried quoting every variable with single quotes, with the same results...

EDIT : Using DBI for perl

The code now looks like :

foreach my $key (sort keys %caisse)
{
 my $insert = $sql_connection->prepare('insert into etablissement values(?, ?, ?, ?, ?, ?) on duplicate key update');
 $insert->execute($key, $caisse{$key}[0], $caisse{$key}[1], $caisse{$key}[2], $caisse{$key}[3],'');
}

I now have the "on duplicate key" issue. How can I add the "on duplicate key" statement in here ? I tried adding it at the end, just like this :

my $insert = $sql_connection->prepare('insert into etablissement values(?, ?, ?, ?, ?, ?) on duplicate key update');

But it's not working

• Tom Fenech: For a start, you should put double quotes around each of your variables in your bash script to prevent problems associated with word splitting and glob expansion.
• Dave Cross: Rewrite the Perl program to use DBI or, better, DBIx::Class. It will make your life much better.

Answer 1

Instead of quoting the variables yourself, use the DBI module and placeholders:

$db = 'DBI'->connect(...);
my $insert = $db->prepare('insert into etablissement values(?, ?, ?)');
$insert->execute($key, $caisse{$key}[0], $caisse{$key}[1]);

For repeated values, numbered placeholders are usually used:

my $insert = $db->prepare(<<'__SQL__');
INSERT INTO etablissement
 (code_etablissement, nom, contact_ce_nom, contact_ce_tel, contact_ce_mail)
 VALUES (:1, :2, :3, :4, :5)
 ON DUPLICATE KEY UPDATE contact_ce_nom = :3,
 contact_ce_tel = :4,
 contact_ce_mail = :5'
__SQL__
$insert->execute($key, @{ $caisse{$key} }[0 .. 3]);

If your driver doesn't support them (I don't see them mentioned in DBD::mysql), you can workaround it e.g.

my $insert = $db->prepare(<<'__SQL__');
INSERT INTO etablissement
 (code_etablissement, nom, contact_ce_nom, contact_ce_tel, contact_ce_mail)
 VALUES (?, ?, ?, ?, ?)
 ON DUPLICATE KEY UPDATE contact_ce_nom = ?,
 contact_ce_tel = ?,
 contact_ce_mail = ?
__SQL__
$insert->execute($key, @{ $caisse{$key} }[0 .. 3], @{ $caisse{$key} }[1 .. 3]);

or you can play with quote and omit placeholders totally.

• Gui O: I cannot execute my request in the perl script, it have to be in the bash one.
• ThisSuitIsBlackNot: @GuiO Is there a particular reason why you have to do it that way?
• Gui O: The bash program is the main program. It calls some others functions such as "ping" or "get snmp" or "send ssh request". So, all my SQL variables are set in it. Defining SQL vars, or declaring another SQL process in another script is not very clean.
• Gui O: I'll do as @choroba said, but what about the "on duplicate key" clause ? should I place it after values(?, ?, ?) like "on duplicate key update column1=?,column2=?,column3=? and add the values in the execute ?
• AKHolland: In MySQL you can use ON DUPLICATE KEY UPDATE contact_ce_num = VALUES(contact_ce_nom), ... rather than passing repeated parameters to the query.

Answer 2

Using partial answer from choroba :

The final question is "how to use on duplicate key syntax with perl DBI ?"

Like this

my $insert = $sql_connection->prepare('insert into etablissement values(?, ?, ?, ?, ?, ?) on duplicate key update contact_ce_nom=?,contact_ce_tel=?,contact_ce_mail=?');
 $insert->execute($key, $caisse{$key}[0], $caisse{$key}[1], $caisse{$key}[2], $caisse{$key}[3],'',$caisse{$key}[1],$caisse{$key}[2],$caisse{$key}[3]);

The last 3 ARGS are used by the "on udplicate key" syntax...