We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

sql escaping backslashes seen as a syntax error

Hi,

I'm trying to add a field to the request by using aliases in the columns function (it is not documented but seen here as available)

$this->modelsManager->createBuilder()
  ->addFrom('User')
  ->leftJoin('UserDisable', 'User.id = UserDisable.user_id')
  ->columns(array('User.*', 'User.disabled' => 'UserDisable.user_id IS NOT NULL'))
  ->getQuery()
  ->execute();

leading to an error : 

Syntax error, unexpected token DOT, near to 'disabled FROM [User] LEFT JOIN [UserDisable] ON User.id = UserDisable.user_id', when parsing: SELECT User.*, UserDisable.user_id IS NOT NULL AS User.disabled FROM [User] LEFT JOIN [UserDisable] ON User.id = UserDisable.user_id

This one is perfectly expected as it would be the same from the SQL engine.

Though, the right way to do it in SQL would be to add escaping backquotes :

$this->modelsManager->createBuilder()
  ->addFrom('User')
  ->leftJoin('UserDisable', 'User.id = UserDisable.user_id')
  ->columns(array('User.*', '`User.disabled`' => 'UserDisable.user_id IS NOT NULL'))
  ->getQuery()
  ->execute();

But unfortunately, it seems they are not recognized by the ORM : 

Scanning error before 'User.disabled` F...' when parsing: SELECT User.*, UserDisable.user_id IS NOT NULL AS `User.disabled` FROM [User] LEFT JOIN [UserDisable] ON User.id = UserDisable.user_id

Would there be an other way to do it, or to specify the query scanner this characters are allowed ?



5.7k


5.7k

phalcon escaping chars are not working as well :

$this->modelsManager->createBuilder()
  ->addFrom('User')
  ->leftJoin('UserDisable', 'User.id = UserDisable.user_id')
  ->columns(array('User.*', '[User.disabled]' => 'UserDisable.user_id IS NOT NULL'))
  ->getQuery()
  ->execute();

  Scanning error before 'User.disabled] F...' when parsing: SELECT User.*, UserDisable.user_id IS NOT NULL AS [User.disabled] FROM [User] LEFT JOIN [UserDisable] ON User.id = UserDisable.user_id

$this->modelsManager->createBuilder()
  ->addFrom('User')
  ->leftJoin('UserDisable', 'User.id = UserDisable.user_id')
  ->columns(array('User.*', '[`]User.disabled[`]' => 'UserDisable.user_id IS NOT NULL'))
  ->getQuery()
  ->execute();

  Scanning error before '`]User.disabled[...' when parsing: SELECT User.*, UserDisable.user_id IS NOT NULL AS [`]User.disabled[`] FROM [User] LEFT JOIN [UserDisable] ON User.id = UserDisable.user_id

So back to the question : can I use backslashes ` as part of the sql query ?



34.6k
Accepted
answer
edited Oct '15

This way:

[User].[disabled]

However, those words don't look like they need escaping, in this case if you check the generated sql you will see that all identifiers are properly escaped according to the database system used



5.7k

Thanks, that looks the most straight way, I should have try it ;)