1

I understand that you cannot use database or table names as parameters in prepared statements. However, our app allows a user to specify the database name during the install. Is there an abstract or PDO provided way to quote these names (backticks for MySQL, brackets for MSSql, etc)?

CConard96
  • 874
  • 1
  • 7
  • 18
  • 1
    Whitelist them - use an array to check against, `$valid_tables = ["tbl_users", "tbl_posts"];` and then `if (in_array($_POST['table'], $valid_tables))` – Qirel Feb 14 '19 at 11:58
  • 1
    Your database name is part of the connection and wouldn't normally be part of the SQL anyway. This may be set in something like a `.env` file or other configuration file. – Nigel Ren Feb 14 '19 at 12:05
  • There is nothing stopping you doing `CREATE \`$db_name\`` as long as you sanity check the user input and of course check that that dbname is not already in use – RiggsFolly Feb 14 '19 at 12:05
  • @NigelRen Database name can be in queries, e.g. `select colname from dbname.tablename`. – Barmar Feb 14 '19 at 12:14
  • @Barmar - I know which is why I said *wouldn't normally be part of the SQL* rather than *can't be in SQL*. – Nigel Ren Feb 14 '19 at 12:17
  • @NigelRen But since his application allows callers to specify the database name, he's apparently using dynamic databases. So the default database specified at connection time is not relevant. – Barmar Feb 14 '19 at 12:19
  • @Barmar they say *our app allows a user to specify the database name during the install* this isn't on a per call level (AFAIK) but when they install it. That doesn't need any dynamic SQL, just that the connection is pointed at the database they specified when they installed the app. – Nigel Ren Feb 14 '19 at 12:26
  • You're right, I missed that detail. – Barmar Feb 14 '19 at 12:27
  • There's still the issue that PDO requires it to be substituted into the DSN string, `dbname=$dbname`. This needs to be sanitized to prevent other options from getting into the DSN. – Barmar Feb 14 '19 at 12:29

1 Answers1

1

You cannot directly escape column names and table names in PDO. You can see the answer here:

escaping column name with PDO

What you can do in this situation is to make a query to get all the tables from the given database, like this:

SHOW TABLES;

Or query to get all Databases like this one:

SHOW DATABASES;

Then use this as a white-list for the user input. When you're using databases, it's wise to exclude some system databases like mysql itself and information_schema.

Other option is to filter the user input with a given regex, for example if your table/database names are only strings with underscore you can use:

preg_match('/^[a-z_]+$/i', $userGivenTableName)

This should remove any potential strings containing SQL Injections.

vuryss
  • 1,270
  • 8
  • 16