-3

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?

donbeshy
  • 1
  • 6
    Maybe you should have a whitelist of column names that can be used? After all you should know, what columns are in your users table. Of course you still have to use backticks, if column name is a reserved keyword. – Hendrik Mar 17 '22 at 10:09
  • @Hendrik I just want to know if it's safe to use only backticks or not) – donbeshy Mar 17 '22 at 10:15
  • Safe as in what way? For example it doesn't help you in any way if the column name does not exists, you'll get an error ```Unknown column 'nmae' in 'where clause'```. What's the use case here anyways, how should the user know the names of the columns? And to answer your question, I personally don't know if you can escape out of these backticks if $name can't contain one, I can't come up with a way from the top of my head. – Hendrik Mar 17 '22 at 12:20

2 Answers2

2

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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • The fact is that there can be many tables in the database. I can't create a whitelist for each individual table. I need a more automated approach. – donbeshy Mar 17 '22 at 10:13
  • Can't information_schema.columns be used? – jarlh Mar 17 '22 at 10:14
  • 1
    Then you're doing it wrong. No whitelist checking means you're allowing people to "guess" column names. That is one of the first things attackers are interested in. – Salman A Mar 17 '22 at 10:14
  • @SalmanA why is it not safe? Could you give an example? – donbeshy Mar 17 '22 at 10:17
  • I know. A whitelist is a safe solution, but I just want to know why is it NOT safe without a whitelist? I just want to see some example of it) – donbeshy Mar 17 '22 at 10:19
  • Well don't be surprised if you notice that someone is trying column names like password, passwordhash, passhash on that page. – Salman A Mar 17 '22 at 10:28
  • @SalmanA I am aware of this, and I have a blacklist for this purpose, because it is much easier and I don't need to put many columns there, unlike a whitelist. So, could you give another example related to safety? – donbeshy Mar 17 '22 at 10:41
  • 1
    I am sorry but looks like you've made up your mind. End of discussion. – Salman A Mar 17 '22 at 10:42
1

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

Rick James
  • 135,179
  • 13
  • 127
  • 222