1

Given the following:

let sql: any = 'SELECT * FROM test_people ORDER BY :column :direction LIMIT :limit, :offset';
let binds: any = { column: 'name', direction: 'desc', limit: '1', offset: '10' };

let result = await mysql.query(sql, binds);

For whatever reason it throws mysql syntax error, if I replace the bindings and write it hard-coded without the bindings then the query actually works and fetches the result. not sure what is wrong here. help ! :)

BTW, I also tried it with the question marks version, getting same syntax error.

Error output:

 ...
 code: 'ER_PARSE_ERROR',
 errno: 1064,
'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \':column :direction LIMIT :limit, :offset\' at line 1',
 ...

Appreciate any solution,

Eden Reich
  • 387
  • 2
  • 7
  • It looks like it needs an array being passed like: let result = await mysql.query(sql, [ binds ]); – DZDomi Sep 16 '18 at 11:03
  • I know this question is about a completely different language, but I'm willing to bet the reason is the same: https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter Parameter bindings are for separating data from the query, not for building dynamic queries. – IMSoP Sep 16 '18 at 11:05
  • @DZDomi so I already tried it with the array version and the question marks, didn't work. getting same syntax error – Eden Reich Sep 16 '18 at 11:06
  • Please remember to include the *exact* error message in questions like this: even if it doesn't mean much to you, it might help someone spot the problem and help you. – IMSoP Sep 16 '18 at 11:09
  • @IMSoP you are right. I updated my question with some more details. btw I remember having same issue with PHP but I solved it, with node library there must be a way similar solving this issue. I will take a look on what you sent me. thanks! – Eden Reich Sep 16 '18 at 11:13
  • @IMSoP after looking on my PHP projects. I solved it using this way. and it did infact worked $statement = $this->connection->prepare('SELECT * FROM ' . $table . ' LIMIT :limit OFFSET :offset'); $statement->execute([ 'limit' => $limit, 'offset' => $offset ]); – Eden Reich Sep 16 '18 at 11:30
  • @IMSoP not sure why it doesn't work with node/mysql thought, doesn't it should be the same concept ? :S – Eden Reich Sep 16 '18 at 11:33

1 Answers1

2

Only values can be bound. Column names (in ORDER BY), and the :direction cannot be bound. Also FYI table names, database names and other parts of the SQL syntax cannot be bound.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • Ok, yeah seems like it...thanks! that actually makes perfect sense, any suggestion / good practices on how to handle dynamic data from the client ? say the client want DESC instead of ASC how should I use it in my queries ? do you escape it by urself ? I was thinking maybe to check if var direction = ('param' === 'DESC') ? 'DESC' : 'ASC'; is that good enough? what about column names ? or LIMIT ? how do you implement those? – Eden Reich Sep 16 '18 at 12:11
  • I think your approach to `ASC`/`DESC` is good enoug. column names, if you sanitize the input properly. Was `LIMIT`/`OFFSET` affected? – danblack Sep 16 '18 at 12:50
  • sorry for the late reply, was on the roads, LIMIT and OFFSET also requested to be dynamic (I guess you already figured that out...its for datatables project). Anyways thanks a lot for the direction, I would try to find a way to sanitize the rest, or maybe I would leave it like it is sense its only for demo purposes.. thanks again ! :) – Eden Reich Sep 16 '18 at 15:59