0

I have this SQL Query which I want to sanitize:

SELECT * FROM navigation_$cat ORDER BY parent ASC, prio ASC

The problem is that I can't get the $cat sooner because it's being send via AJAX, and indicates which database needs to be accessed. How can I create a PDO statement that is safer than simply putting a string together?

smiet
  • 19
  • 7
  • Where does `$cat` come from? – BenM Aug 09 '17 at 18:42
  • 4
    [I do not believe you can parameterize table names using PDO.](https://stackoverflow.com/q/11312737/2191572) You can/should build a whitelist of acceptable values for `$cat` and check if `$cat` is in the whitelist before building your query string. – MonkeyZeus Aug 09 '17 at 18:42
  • 1
    Check $cat is in a list of expected values – Ed Heal Aug 09 '17 at 18:43
  • 1
    Is there a finite set of possible values of `$cat` known in advance? If so, you can compare the user input against the possible known values (which you control and is not SQL injectable). If the input matches a known value, use the value you control. If it doesn't don't continue with the query. – David Aug 09 '17 at 18:43
  • Unless `$cat` is being built by user-supplied data (as opposed to you defining the possible values), sanitization is a minor concern. – RToyo Aug 09 '17 at 18:43
  • 1
    Is it possible to use partitioned table on your DB so that you don't have to parametrize db name but select on a specific column ? – wargre Aug 09 '17 at 18:45

1 Answers1

2

You can implement a whitelist, but another option would be to check for valid input using:

SHOW TABLES LIKE :tblname

and 'navigation_'.$cat as the parameter. Check that this returns exactly one table, and that the returned table exactly matches the passed parameter.

Once that's done, you know it's safe to inject into the query, because you've ascertained that it is indeed a valid table name.

That being said, "dynamically selecting from an unknown-in-advance table" is usually a sign that you're designing your database wrong, and you should probably just have one navigation table with a category column inside.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • Thanks for the tip! I actually already have a table with the categorise, I just thought I could skip it a this point. Changed my code now so that properly works. Thanks! – smiet Aug 10 '17 at 09:48