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.

CodePudding user response：

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.

CodePudding user response：

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.