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.
>Solution :
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
LIMITis an integer, and not something unsafe coming right from the UI like3; DROP TABLE EMPLOYEE. - For the
ORDER BYclause 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.