Should you use prepared statments on LIMIT

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.

Answer

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.