Avoiding SQL injections with prepare and execute

Question

A line of code like

my $sql_query = "SELECT * FROM Users WHERE user='$user';";

might introduce an SQL injection vulnerability into your program. To avoid this one could use something like

my $sth = $dbh->prepare("SELECT * FROM Users WHERE user='?';");
$dbh->execute($user);

However, in the code I am currently working on the following is used

$sql_query = "SELECT * FROM Users WHERE user='" . $user . "';";
$dbh->prepare($sql_query);
$dbh->execute();

Does this actually work? If yes, are there any differences to what I would have done? What are the advantages and disadvantages?

Answer 1

my $sth = $dbh->prepare("SELECT * FROM Users WHERE user='?'");

This won't work because it's searching for a literal '?' character — not a parameter. If you try to send a value for the parameter, MySQL will be like, "what do you want me to do with this?" because the query has no parameter placeholder.

If you want to use a parameter, you must NOT put the parameter placeholder inside string delimiters in the SQL query, even if the parameter will take string or datetime value:

my $sth = $dbh->prepare("SELECT * FROM Users WHERE user=?");

The next example:

$sql_query = "SELECT * FROM Users WHERE user='" . $user . "'";
$dbh->prepare($sql_query);
$dbh->execute();

That will run the query, but it's NOT safe. You can prepare any query even if it has no parameters.

Using prepare() is not what makes queries safe from SQL injection. What makes it safer is using parameters to combine dynamic values instead of doing string-concatenation like you're doing in this example.

But using parameters does require the use of prepare().

PS: You don't need to put ; at the end of your SQL queries when you run them one at a time programmatically. The separator is only needed if you run multiple queries, like in an SQL script, or in a stored procedure. In your examples, the ; is harmless but MySQL doesn't require it, and it will just ignore it.