placeholder use in perl DBI

Question

I have perl script as following

my $tb = 'rajeev';
$query = 'select * from table where name = ?'
$sth = $dbh->prepare($query);
$sth->execute($tb);

Does $tb replaced by rajeev or 'rajeev' when query executes ? means does query executs as
select * from table where name = rajeev
or
select * from table where name = 'rajeev'

Answer 1

DBI handles all the escaping for you. In the case of a string, it will be 'rajeev'. Calling select * from table where name = rajeev will give you an error.

If you provide a number, it will not add quotation marks because they are not needed.

See the DBI Doc. It also says:

The quote() method should not be used with "Placeholders and Bind Values".

Using placeholders sometimes takes care of the quoting for you, depending on which DBD you are using. In your case the DBD::mysql calls $dbh->quote() as mentioned in the doc:

An alternative approach is

$dbh->do("INSERT INTO foo VALUES (?, ?)", undef, $number, $name);

in which case the quote method is executed automatically.

If you have access to the query log you can check what the queries look like. If you have queries that take a long time you can also open a mysql console and say SHOW FULL PROCESSLIST; to see a list of the running queries. That will also hold the complete SQL statements for you to look at. On Windows you could use HeidiSQL to do it.