I am reviewing an application that has something like this:

table_name = table_name.replace('`', '')
c.execute('SELECT * FROM `' + table_name + '`')

Is this actually exploitable if the attacker controls the table_name field? The language is python and the database is sqlite fwiw

  • 7
    Please do not ever, under any circumstances concatenate strings to form SQL. – Stephen Touset Apr 25 '13 at 1:51
  • Yes, but how would you exploit it in this situation? – bwbrowning Apr 25 '13 at 2:10
  • ending table_name with \ causes some trouble, but I see no way to exploit it in your example. – CodesInChaos Apr 25 '13 at 9:20
  • 1
    Chances are you'll be having more than a single input parameter like this in your queries. If that's the case, then you need to consider also the possibility of commenting out some parts of your queries to either concatenate two separate input parameters into a new one (that might not be detected as unacceptable when in two parts), or completely bypass one or more of them (along with the rest of your code in-between). For SQLite, that's -- to comment out a single line, and /* and */ pair to comment out a larger block of SQL code. – TildalWave Apr 25 '13 at 10:31
  • How about unicode smuggling? Can an attacker inject the unicode backtick and comment out the rest of the code? – cagryInside Sep 18 '18 at 15:54

There's a few things an attacker could do with that code that you might not want.

  • If their input is SQLITE_MASTER then they could retrieve metadata.
  • If their input is databasename.tablename, then they could retrieve data from another database.
  • If their input is (select load_extension(...) from tablename) they might be able to load an extension.

Or they could run any select query they wanted by wrapping it in parentheses.

| improve this answer | |

Sometimes you do have to concatenate strings to make SQL statements. You must of course be very careful under such circumstances. Variable table names are one such situation.

You should probably in this case use a built-in like quoteIdentifier to ensure you cope with all necessary circumstances.

I would also in addition add a restriction on the allowable characters:

 if(table_name.match(".*[^a-zA-Z_].*")) throw_big_error();

Yes, that limits table names beyond what the underlying RDBMS supports.

Do you have a requirement to support table names with spaces in them? Or is it acceptable to forbid special characters in table names? If you can, do so.

"Just do it right instead of asking whether doing it wrong is safe".

Since you are reviewing code, this is the comment to the coder.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.