0

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();
// ...
Dharman
  • 30,962
  • 25
  • 85
  • 135
keewee279
  • 1,656
  • 5
  • 28
  • 60
  • 2
    If you are in control of the variable you're concatenating you're not susceptible to SQL injection. –  Jul 20 '15 at 05:52
  • Thanks. I am not sure about this. The language variable depends on a dropdown selection and is then passed along as a variable through the Ajax call that uses the above ($language = $_POST["language"]) - does that make it secure enough ? – keewee279 Jul 20 '15 at 05:54
  • 3
    Presumably you're only supporting a limited number of languages. Check the `$_POST` variable against a whitelist and reject anything you don't find there. You should be fine. –  Jul 20 '15 at 05:57
  • Thanks again ! Yes, you are right about the number of languages. How would I check the variable against a whitelist ? Is that something you could post as an answer ? Will be happy to accept then. :) – keewee279 Jul 20 '15 at 05:59
  • 1
    Look here: http://php.net/manual/en/function.array-search.php - setup an array with valid col names as @HoboSapiens wrote. If not found, discard.any action – Axel Amthor Jul 20 '15 at 06:01
  • @AxelAmthor: Thanks for this - I'll have a look. – keewee279 Jul 20 '15 at 06:03
  • @HoboSapiens Using a white list means hardcoding the list of languages or adding an extra table. If the database is to be altered, I would rather normalize it. – Tarik Jul 20 '15 at 06:07
  • @AxelAmthor: The link you posted is perfect and resolves this for now - thanks a lot. I am going to accept Tarik's answer since this is what I need to do in general. – keewee279 Jul 20 '15 at 06:10
  • @Tarik There are lots of possible solutions to this, but they all amount to the same thing: validate the input against a known list. The implementation details are immaterial. –  Jul 20 '15 at 06:33
  • @HoboSapiens Sure, there is more than one way to skin a cat. I proposed the one that I thought to be less painful in the long run :-). Seriously speaking, some problems arise out of inferior design and I would rather treat the illness than the symptoms. – Tarik Jul 20 '15 at 16:57
  • Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Dharman Oct 06 '19 at 15:40

1 Answers1

0

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.

Tarik
  • 10,810
  • 2
  • 26
  • 40
  • Thanks. You are right regarding the normalization. I was planning on doing this in a later step when I know the basics are working since this is the first bigger website I am working on. :) – keewee279 Jul 20 '15 at 06:04