Search code examples
phpmysqliprepared-statement

How to prevent SQL injection when using variable column name for Select

I am trying to prevent SQL injection in a Select statement. When this is just about values (as for the Like part here) I use "bind_param" as in the example below which works as intended.

However, I am having issues with the variable column name since I cannot use "bind_param" for this.

Can someone tell me how I can prevent SQL injection for the variable column name ($language) as well (the current code is working)?

My PHP:

$language = "some language";
$location = "some location";
// ...

$stmt = $conn->prepare("SELECT tID, " . $language . " FROM Main WHERE location LIKE ? ORDER BY sortOrder, " . $language);
$stmt->bind_param("s", $location);
$stmt->execute();
// ...
Solution

  • You can select all columns anyway, then try to access the column name using the selected language within the result set. If the language is incorrect, you will get an exception that you can handle.

    A better solution would be to normalize your database design to have the language in rows instead of columns. You could then use a parametrized query without problem.