Using db::query with prepared statements?

Kirby 2 Solved ✅
1

Here is what the docs says:

https://getkirby.com/docs/cookbook/database

$result = db::query('SELECT * FROM comments JOIN users ON comment_user = user_id ORDER BY comment_date DESC LIMIT 0,10');

Warning: If you write your own SQL queries, you must make sure that they are protected against SQL injections.

I like to write my “raw” SQL queries, because then I can create more complex queries without boundaries. However, I want to protect these from SQL injections. Is it possible to use the db::query helper with something similar to how it works below with prepared statements?

http://php.net/manual/en/pdostatement.bindparam.php

$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();
2

You can use binding, like this:

$query = $db->query('select * from users where name = :name', ['name' => 'Homer']);

Or in your example:

$calories = 150;
$colour = 'red';
$sth = $db->query('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour', ['calories' => $calories, 'color' => $color]);

Don’t know how to add these additional parameters, I’m afraid. But then again, you’d probably rather use Kirby rules/validators on user input, anyway, so no need for these PDO flags?

1 Like
3

That works perfectly, thanks! Maybe add it to the docs? Or I’ll add it to Kirby Secrets.

4

Think that should be in the docs, I’ll put it on the to do list. The database docs are a bit neglected, I’m afraid. Haven’t used the class myself a lot because of that and turned to standard PHP code when I needed it :innocent: