4

What would be a good check to remove all possibility of SQL-injection on an ORDER BY col where col is an unsafe variable?

I'm currently removing [^A-Za-z0-9_] from the string in php. Is this too paranoid or maybe even not enough?

EDIT:

Just to clarify: I'm looking for a way to remove any SQLi from an ORDER BY $unsafeVariable - not a general SQLi-prevention solution. I'm already using prepared queries and pdo::quote where needed but since I needed to include an unsafe variable in a order by statement I thought I'd ask for someone elses opinion.

Improve this question

3 Answers 3

Reset to default
3

Best solution is if you know all allowed ORDER BY columns in advance and check your $unsafeVariable against that whitelist.
Another option: after removing all invalid characters check that your $unsafeVariable is a substring of the query you are building. It would become extremely difficult to misuse that.

Improve this answer
2
  • Could you give an example of your 2nd option?
    – Filip Haglund
    Jan 30, 2013 at 19:44
  • 1
    +1 for the whitelisted columns, which is probably the only real option. Although, I'd probably anticipate the provided variable being an 'alias' (as a key to a hashmap with the real column name), as otherwise you're exposing implementation details to the outside world. There's a variety of ways to get the list, but that's somewhat unimportant. Perhaps the best reason to use a whitelist - generally speaking, for performance reasons, you'd want to limit ORDER BY clauses to using columns backed by an index.
    – Clockwork-Muse
    Jan 30, 2013 at 19:59
1

Please don't feel i am just giving few links and not explanations. I thought all the answers are there in that links and it will be good rather than copying the same text to here. If you want to check more about security then that links will be useful.
Just check https://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php and get an idea about how to prevent sqli in php.
To read more about php security read https://www.owasp.org/index.php/PHP_Security_Cheat_Sheet.

Improve this answer
0

I consider your solution as first layer protection from SQL injection or any code injection attacks (XSS), it's a requirement to clear user-input data before sending it to database, I confused with some people that recommending just prepared query (parametrized query) and using stored procedure, because there is no logic to send unsafe parameter to database if can be cleaned before, but, for better security, using mentioned methods are also required.

(note: the idea behind parametrized query is that database can distinguish between the original query and the data that used for building that query) (owasp, 2012)[4]

some rules that I use them always:

  1. Use latest update/latest technology (update frequently, install patches and fixes)
  2. Clean user-input data before using it inside query
  3. Use less privilege user when building dynamic query, delete privilege not required for select query
  4. Monitor integrity of files and critical data.

Useful links:

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .