0

I have a statement similar to this, where 'name' can be inserted

select * from table where column in (/**name*/ 'name')

I am currently checking for ')'.

ex. they can put in '*/; drop table--', but sql will throw error because no parentheses right?

If they cannot close the parentheses, is there still a security risk?

dwurf
  • 12,393
  • 6
  • 30
  • 42
user1902660
  • 1
  • 1
  • 2
  • What database are you using (e.g. MySql, Oracle, MS Sql Server)? – JohnLBevan Dec 14 '12 at 00:33
  • 4
    *Use placeholders for all data* - this prevents all injection locations where data can be bound. For other places where dynamic non-data is used (e.g. column names) consider deconstruction/reconstruction and whitelisting. –  Dec 14 '12 at 00:34
  • There is little reason to check for these specifics of security. You should have one function that cleans the user input entirely and simply call it repeatedly. – Grumpy Dec 14 '12 at 00:34
  • 1
    http://stackoverflow.com/questions/1508431/how-to-demonstrate-sql-injection-in-this-php-and-mysql-code?rq=1 , http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection –  Dec 14 '12 at 00:38
  • It's a framework that's been here, (bad coding on their part) but we want to patch it up temporarily. really, i just want to redo the framework. Still, is there some way that a malicious user could bypass closing the parenthese? – user1902660 Dec 14 '12 at 01:23
  • 1
    Surely it is apostrophes and the `*/` sequence that you should be worried about, not parentheses. Those are the immediate delimiters on the two instances of `name`. – bobince Dec 14 '12 at 10:31
  • Don't waste your time writing and testing new code trying to sanitize data. Use placeholders in parametrized queries where the work is all done for you. http://bobby-tables.com/php.html gives you some examples. – Andy Lester Dec 15 '12 at 19:44

4 Answers4

3

Why risk it? Always use SQL query parameters for dynamic values, then you don't need to worry about escaping or whether you're at risk.

If you specify what programming language and RDBMS brand you're using, I'll point you to a resource with examples of using query parameters.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • It's a framework that's been here, (bad coding on their part) but we want to patch it up temporarily. really, i just want to redo the framework. Still, is there some way that a malicious user could bypass closing the parenthese? – user1902660 Dec 14 '12 at 01:22
  • Yes, they could inject a closing parenthesis. – Bill Karwin Dec 14 '12 at 01:53
2

Yes - what if they terminate the statement (albeit invalid), and then execute their own?

This works (tested it locally on mysql):

select * from TABLE where (col = ''; select * from TABLE;

The first statement generates an error, but the second statement runs fine.

jchapa
  • 3,876
  • 2
  • 25
  • 38
  • Most APIs do not support multi-query by default. – Bill Karwin Dec 14 '12 at 00:32
  • 1
    I just ran that from command line mysql (5.x enterprise-commercial-advanced). It looks like at least mysql likes to support it by default (not sure if languages override it, though): http://dev.mysql.com/doc/refman/5.1/en/c-api-multiple-queries.html – jchapa Dec 14 '12 at 00:36
  • 1
    I take it back. PDO seems to support multi-query by default in both `PDO::exec()` and `PDO::query()`. At least in Mysqli, you have to deliberately shoot yourself in the foot by calling `mysqli::multi_query()`. – Bill Karwin Dec 14 '12 at 00:52
  • I'd say the mysql client is not a programming interface, it's meant to run multiple queries separated by semicolons, either interactively or from an SQL script, but not from a programming language. – Bill Karwin Dec 14 '12 at 00:53
0

Yes.

If I take your post literally, you want to insert name in two places, once in the comment and once in the text string:

select * from table where column in (/**/select column from table where column !=/**/ '/select column from table where column !=/*');
dwurf
  • 12,393
  • 6
  • 30
  • 42
-1

Always escape any user-inputted data. 

mysqli_real_escape_string

Or old school

mysql_real_escape_string
bobthyasian
  • 933
  • 5
  • 17