Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

[RawSQL] Cannot bind string has `/` character to find in DB

I have table urlTable contain url like this:

id    url
1     abc.com/zyx.html
2     abc.com/xyz123.html

To find one of them, i use the RawSQL:

$url = 'abc.com/zyx.html';
$sql    = "SELECT id FROM urlTable WHERE url = ':url'";
$result = \Phalcon\DI::getDefault()->getDb()->fetchAll($sql, \Phalcon\Db::FETCH_ASSOC, ['url' => $url]);

But result is empty. SQL log is:

SELECT id FROM urlTable WHERE url = ':url' [{"url":"abc.com\/zyx.html"}]

So, phalcon has escaped the / character to \/.

I have what I need when I use:

$url = 'abc.com/zyx.html';
$sql    = "SELECT id FROM urlTable WHERE url = '$url'";
$result = \Phalcon\DI::getDefault()->getDb()->fetchAll($sql, \Phalcon\Db::FETCH_ASSOC);

SQL log is:

SELECT id FROM urlTable WHERE url = 'abc.com\/zyx.html'

What is wrong when I use the first way to avoid SQL injection?

Hi @Envy the problem is the bind parameter :url: doble colon

$sql = "SELECT id FROM urlTable WHERE url = :url:";

Good luck



3.3k
Accepted
answer
edited Aug '17

Thank so much, @Degiovanni.

However, I found out the problem is the single quote, not the colon. With rawsql, we just need one colon and in this case, we don't need single quote.

$sql = "SELECT id FROM urlTable WHERE url = :url";


14.5k

What's the different between one colon and double? Is it the function of PHP PDO or phalcon binding parameters?

edited Aug '17

Single colons :param are used by PDO (db->execute())

Double colons :param: are used by PhalconQL (modelsManager->createQuery())