Should you use prepared statments on LIMIT

Question

One day I was googling to understand when a prepared statment should be used. As I searched online many claimed that you should use always prepared statments. Now the question I have is... does this also count for LIMIT? I mean it is possible (I tried) but is it really that rational? Same question on ORDER BY too.

You should **always** use prepared statements... when possible, of course. — The Impaler, Jul 20 '22 at 15:08
Different databases impose restrictions on the location of parameters. For example DB2 does not allow parameters for the LIMIT clause, and you can only use Controlled SQL Injection to implement it. What database are you using? — The Impaler, Jul 20 '22 at 15:09
Best advice: For **any** and **all** values with dynamic data, no exceptions. `LIMIT 1` is fine. `LIMIT $limit` is how you [end up on this list](https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/). — tadman, Jul 20 '22 at 15:16

The Impaler · Accepted Answer · 2022-07-20T15:22:08.047

When the database does not allow you to use a parameter on a specific location of the SQL statement you need to assemble the query on the fly, by the use of Dynamic SQL. That is... concatenating strings to get a full functioning SQL query.

Now, the trick is to make it safe against SQL Injection. To do this:

Make sure the value for LIMIT is an integer, and not something unsafe coming right from the UI like 3; DROP TABLE EMPLOYEE.
For the ORDER BY clause make sure the columns are not coming from the UI "as is"; use some kind of projection. For example, if there are 50 columns to order by, the UI can display them all, but then just send a number (from 1 to 50) to the backend; the backend receives this number and reconstitutes the ordering column(s) from it.

score 2 · Answer 2 · answered Jul 20 '22 at 15:20

Normally the LIMIT parameters must be literals, not values that can be substituted for placeholders, so you would have to validate that they're integers before substituting into the string.

However, if you use PDO rather than mysqli, it allows you to perform parameter substitution in the LIMIT clause, by using the PDO::ATTR_EMULATE_PREPARES option. This is automatically enabled for a specific prepared statement if it contains parameters in this clause. See How to apply bindValue method in LIMIT clause? for the specific details.

Should you use prepared statments on LIMIT

2 Answers2