CODEWITHSUNDEEP
mysqlperl
prgrm
prgrm

Reputation: 3833

SQL statement fails under Perl but works from the command line

I am trying to execute a script to update a database:

my $sql_hash_update = 'UPDATE user SET hash = $hash , updated = 1 WHERE id = $row[0]';
my $sth_hash_update = $dbh->prepare($sql_hash_update);
$sth_hash_update->execute();

I get the error that this is not proper syntax, but this works within SQL itself.

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[0]' at line 1 at conexion.pl line 32.

Any ideas what am I doing wrong?

Upvotes: 0

Views: 174

Answers (3)

Borodin
Borodin

Reputation: 126722

You are using single quotes, so this statement

my $sql_hash_update = 'UPDATE user SET hash = $hash , updated = 1 WHERE id = $row[0]'

will not interpolate the values of $hash and $row[0] into the SQL statement. Instead they will be left as they are, and so the statement isn't valid SQL

You could simply switch to double quotes, which do interpolate, but it is best to use placeholders like this

my $sql_hash_update = 'UPDATE user SET hash = ?, updated = ? WHERE id = ?';

my $sth_hash_update = $dbh->prepare($sql_hash_update);

$sth_hash_update->execute( $hash, 1, $row[0] );

That way you avoid the risk of code injection, and you need to prepare only once for many different execute calls

Placeholders are valid wherever an expression is allowed in the SQL syntax. That means, for instance, you cannot provide a placeholder for a table name, because you couldn't put an expression there in an ordinary SQL statement

Upvotes: 5

PravinS
PravinS

Reputation: 2584

Use double quotes instead of single quote

my $sql_hash_update = "UPDATE user SET hash = $hash , updated = 1 WHERE id = $row[0]";

Upvotes: 1

Steven
Steven

Reputation: 406

Perl does not interpolate single quotes so $row[0] is not being expanded. You want double quotes.

However, you should also pass $row[0] as a bind parameter.

Something like:

my $sql_hash_update = 'UPDATE user SET hash = ? , updated = 1 WHERE id = ?';
my $sth_hash_update = $dbh->prepare($sql_hash_update);
$sth_hash_update->execute($hash, $row[0]);

Upvotes: 1

Related Questions