0

I need to use dynamic table names which can't be provided as parameters in prepared statements. I figured that if I restrict identifier names to alphanumeric + underscore only, then there is no way some sql could be injected, right?

here's the check function:

function is_safe_identifier($s) {
    return preg_match('/^[a-zA-Z0-9_]+$/', $s) == 1;
}
user81993
  • 6,167
  • 6
  • 32
  • 64
  • 1
    Don't you know the allowed column names beforehand? So, whitelist them. – zerkms Mar 20 '17 at 21:10
  • 1
    TL;DR on the dupe: NO, it's not enough. What you should do is whitelist the parts you can't prepare. In other words, make a list of tables and ensure it's part of that list – Machavity Mar 20 '17 at 21:12
  • There's little change of SQL injection if they cannot be provided as parameters in prepared statements. ;-) To be honest, I think this will do the trick, though many people would like to see even more protection. Make sure you **bind** the parameters. – KIKO Software Mar 20 '17 at 21:13
  • You could make a prepared statement which checks the table name against the catalog – Turo Mar 20 '17 at 21:17
  • @Machavity why not? what combination of the allowed characters could possibly inject SQL? – user81993 Mar 20 '17 at 23:16
  • @user81993 Because you can figure out your DB structure via [trial and error](https://www.owasp.org/index.php/Testing_for_SQL_Server#Trial_and_error). You should expose as little of your application as you can – Machavity Mar 21 '17 at 00:07
  • @Machavity the structure is not a secret though and a whitelist wouldn't do anything against that anyways – user81993 Mar 21 '17 at 07:13

0 Answers0