0

I want users to be able to enter a limit for a query. As part of mysql syntax, the LIMIT can't have quotes around it, and can have digits and a comma. It would be very easy to do this:

preg_replace(/[^\d,]/, '', $request->post('limit'))

...however, this is insecure since the sanitation is not done at the DB class level.

What is the proper way to sanitize inputs to queries that are not parameters (such as column names, limit amounts, etc.)?

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • I had about 4 different ideas at the time I wrote that useless comment I just deleted. I'm pretty confused. Sorry about that. – inhan Jan 26 '13 at 17:50

2 Answers2

0

There is only one 100% safe way of treating user-provided input that goes into queries but is not a parameter: whitelist the input you would want to accept.

For LIMIT and similar numeric quantities you can simply cast the parameter to int. For column names the only sane thing is to check that the name is exactly equal to one of the column names in the database.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • That's all easy stuff, but the problem is I want this to be done on the DB layer. To me, this is like doing `mysql_escape_string`. It works, but it has to be done in a specific spot, you can forget, it can be lost when things get moved around, etc. I'm using PDO, but their docs don't seem to have anything that covers this. – Explosion Pills Jan 26 '13 at 16:35
  • @ExplosionPills: There is no functionality that covers this, so it's manual all the way. Even the comparatively more reasonable `WHERE x IN (...)` where you have the values in an array needs to be done manually by generating enough placeholders in an SQL string. – Jon Jan 26 '13 at 16:37
0
  1. LIMIT amounts are perfectly parameterizable. If your PDO failed on them - just turn emulation off
  2. For the identifiers - alas! - PDO have no solution. Format them manually, by enclosing in backticks and doubling these backticks inside
  3. For the keywords whitelisting is the only solution.

As you can see, whole your question improperly stated, as there is no single way to "sanitize" all different query parts. That's the point. Most people (including authors of PHP extensions) do not understand that different query parts require different formatting.
And formatting is another key word. Because there is no "sanitizing" - there is nothing to sanitize actually, but rather few quite simple rules required by the SQL syntax. Required despite of all injections but just as a syntax rule. Yet as long as you have all your query parts properly formatted, you're safe against injections too, just as a side effect

And very few come to conclusion that standard placeholder should be extended to several different types.
So, in my class I have 5 at the moment: 3 for literals (strings, integers, identifiers) and 2 for complex data sets going into IN and SET operators.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • What do you mean turn emulation off? What does that do, and how can I use `LIMIT` like that? – Explosion Pills Jan 26 '13 at 17:18
  • [You can see it here](http://stackoverflow.com/a/8265319/285587) (As well as other useful explanations, I hope). After turning it off you can use LIMIT parameters with placeholders, just usual way - `LIMIT ?,?` – Your Common Sense Jan 26 '13 at 17:25