2

I'm trying to work with mysql prepared statements. I'm wondering if there's the possibility of numbered placeholders like I can use in sprintf or vsprintf. E.g.:

<?php
$format = 'The %2$s contains %1$d monkeys';
echo sprintf($format, $num, $location);
?>
Alexander Behling
  • 507
  • 3
  • 24

1 Answers1

1

It depends on which SQL library you use within PHP.

If you use the PDO library you can use named parameters, which comes to the same thing as using numbers. http://php.net/manual/en/pdostatement.bindparam.php gives an example of this:

$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();

In the above, since the parameters are named, it would not matter in which order you supply the "calories" and "colour" variables, or where they occur in the statement.

However mysqli does not support this, and instead you have to use simple ? placeholders, and then supply the parameters in the exact order they are to be used - see http://php.net/manual/en/mysqli-stmt.bind-param.php.

ADyson
  • 57,178
  • 14
  • 51
  • 63
  • One of the big reasons I don't use mysqli.. I don't know what it is.. I just don't like `?` placeholders – Isaac Dec 18 '18 at 09:30
  • As far as I know, PDO emulates this behavior. Internally, sprintf is probably used. Also, I've read that PDO should be slower than MySQLi. No idea if this is true. So I wanted to use MySQLi. So I have to use MySQLi to write the same variable in bind_result as often as I use in the query. The question that arises is whether to do the generation of the query with MySQLi better with sprintf and send the string variables first through mysqli_real_escape_string. – Alexander Behling Dec 18 '18 at 13:15
  • @AlexanderBehling "As far as I know, PDO emulates this behavior." ...that depends on the value of `PDO::ATTR_EMULATE_PREPARES` (see http://php.net/manual/en/pdo.setattribute.php) - whether you emulate prepares or not should depend on whether the underlying DB driver supports native prepared statements. If it does, you should use native ones wherever possible. – ADyson Dec 18 '18 at 13:41
  • @AlexanderBehling mysqli_real_escape_string can be circumvented in obscure cases (https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/12118602#12118602) so ideally you should use prepared statements. Plus it makes it less likely you'll introduce unexpected syntax errors. As for whether PDO is slower than mysqli or not, I would think that could depend on a lot of factors. Have you got any hard evidence of it? – ADyson Dec 18 '18 at 13:44
  • @ADyson Thanks for pointing this out. But it not clear to me what are the benefits of not using (v)sprintf instead of prepared statements. It would give me the same result. – Alexander Behling Jan 11 '19 at 09:39
  • @ADyson If the someone run something like "';SHOW DATABASES" whether I use prepared statements or (v)spriintf the ' will be masked so mysql will handle it as a string. If the query is a INSERT/UPDATE-statement this waste will be stored in the database. In my opion user input should always be examine for SQL-keywords. If something is found the script should not passed it to the database. – Alexander Behling Jan 11 '19 at 09:48
  • "If the query is a INSERT/UPDATE-statement this waste will be stored in the database" ...that's the user's problem, not yours. If they want to fill your database with meaningless junk they can go ahead, you don't need to care. The only thing you care about as the programmer / maintainer is that it doesn't get executed and hurt your database or data integrity. Sure you can strip SQL keywords if you want...but you know sometimes "insert" will actually be a valid part of a sentence the user is typing and expecting to store in the database. Just like the "inserts"s in this comment, in fact :-) – ADyson Jan 11 '19 at 10:11