How to escape strings passed to database::query()

WindBridges

Hi. Long long time ago we used mysql_real_escape_string(). How to do it now with Database object? Something like “SELECT ".$this->connection->escape($column)." FROM table”

duke

For identifiers (column names and table names), you can use:

$this->connection->getSupplementalDriver()->delimite($identifier);

Values should be bound through PDO via ? symbol.
If you absolutely need to escape values manually, you can use PDO::quote as Nette\Database\Connection is subclass of PDO. I.e.:

$this->connection->quote($value);

Last edited by duke (2012-04-10 00:13)

WindBridges

Thanks!

HosipLan

No, don't use it! You don't need to quote parameters! It's wrong! Just use it the right way!

$db->table('articles')->where('name = ?', $name)->fetch();

or just simply

$db->table('articles')->where('name', $name)->fetch();

Use parameters!

duke

Unfortunately there is currently no support for parameter placeholders in selection part of the query in Nette\Database, but only for where part. Hence the only way to do it properly in such cases is to avoid using both select() and query() methods and use PDO's prepare() and execute() directly.

E.g.:

$query = 'SELECT COUNT(*) AS `total`,
	COUNT(NULLIF(`author`.`name` = ?, 0)) AS `matchesCount`,
	GROUP_CONCAT(CASE `author`.`name` WHEN ? THEN `application`.`title` ELSE NULL END) AS matches
	FROM `application`
	JOIN `author` ON `application`.`author_id` = `author`.`id`';
foreach ($db->prepare($query)->execute(array($author, $author)) as $row) {
	Debugger::dump($row);
}

But of course the same could be possible to achieve even with traditional approach, but not with a single query.

Perhaps select() method should be modified to accept parameters in similar fashion as where() method does. Then it would be possible to write this:

$columns = 'COUNT(*) AS total,
	COUNT(NULLIF(author.name = ?, 0)) AS matchesCount,
	GROUP_CONCAT(CASE author.name WHEN ? THEN application.title ELSE NULL END) AS matches';
foreach ($applications->select($columns, $author, $author) as $row) {
	Debugger::dump($row);
}

Last edited by duke (2012-04-10 22:17)