CODEWITHSUNDEEP
phppdo
Adam
Adam

Reputation: 20882

PHP PDO + Prepare Statement

$sql='SELECT phrase,english FROM static_site_language WHERE page=?;';
$pds=$database->pdo->prepare($sql); $pds->execute(array($_POST['languagepage']));

The above code runs fine. However I need to put another variable into the prepare statement. I have tried the following but it doesn't seem to work:

$sql='SELECT phrase,? FROM static_site_language WHERE page=?;';
$pds=$database->pdo->prepare($sql); $pds->execute(array($_POST['language'],$_POST['languagepage']));

I know $_POST['language'] (from printing it) only contains the word 'english'. Is it possible to put a prepare variable in this part of a select?

thx

Upvotes: 5

Views: 462

Answers (2)

Skrol29
Skrol29

Reputation: 5552

Prepared statement support only parameters that are values supported by the database.

In your second statement, the first "?" is a placeholder for a column name, not a value.

You have to use a dynamic SQL statement instead. For this, you have to prevent from SQL injection.

$language_authorized = array('english', 'french', 'spanish');
$language = $_POST['language'];
if (in_array($language_authorized, $language)) {
  $sql='SELECT phrase,'.$language.' FROM static_site_language WHERE page=?;';
  $pds = $database->pdo->prepare($sql);
  $pds->execute(array($_POST['languagepage']));
}

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562260

Query parameters can take the place of only a constant value -- not a column name.

All columns and tables must be named at the time you prepare a query, you can't postpone choosing columns to the subsequent execute step.

When you want user input to determine a column name, use a Whitelist Map to limit user input to valid choices. The keys of the map array are the legal user inputs. The values of the map array are the strings you want to use in the SQL query, in this case column names.

$lang_col_map = array(
  "DEFAULT" => "english",
  "en"      => "english",
  "es"      => "spanish"
);
$lang_col = $lang_col_map[ $_POST["language"] ] ?: $lang_col_map[ "DEFAULT" ];

$sql='SELECT phrase,$lang_col FROM static_site_language WHERE page=?;';
$pds=$database->pdo->prepare($sql); 
$pds->execute(array($_POST['languagepage']));

This way you can be sure that only values in the $lang_col_map can become part of the SQL query, and if the user tries to send anything tricky in the http request, it's ignored because it doesn't match any key of that map. So the query is safe from SQL injection.

See my presentation SQL Injection Myths and Fallacies for more information.

Upvotes: 7

Related Questions