What is a safe and simple way of escaping table's column names in MySQL?-CodePudding

I googled it, but it's so much information over there. So, I've go the following query:

$name = (string) $_GET['user-input'];

if (str_contains('`'))
    throw \Exception('Error message');

$sql = "SELECT * FROM users WHERE `$name` = 'someUsername'";
// ... query execution comes here

My question is: is It enough (safe) to enclose column names with the "`" character? I don't want to use regExp because I think it has the worst effect on performance.

If I can't use just "`", what should I do then?

CodePudding user response：

Create a whitelist of column names and compare the user supplied value against it:

const valid_column_list = ['username', 'name'];
$user_input = filter_input(INPUT_GET, 'user-input');
if (in_array($user_input, valid_column_list)) {
   $sql = "SELECT * FROM users WHERE `$user_input` = 'someUsername'";
}

That is how it should be done.

CodePudding user response：

Not safe.

As soon as a hacker discovers that backtic is the quoting character you are depending on, they can easily do "SQL injection" to hack your system. They might present your UI with a "name" like "id; DROP DATABASE ...;" (where they have already guessed the name of your database).

Note that disallowing multi-query also helps avoid that type of hack. They could, instead, do something with a subquery.

Face it. You need to have compete control over database/table/column names. (The suggestion of a "whitelist" is one way.)