PHP PDO保护数据库名称

Question

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)?

Answer 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.