PHP PDO + Prepare Statement

Question

$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

no error - but rather then get the value back from the DB it just gives me the word english. This is the value in the variable and title of the column in the DB... — Adam, Dec 30 '11 at 03:07
eg: {"free":"english","meetsingles":"english","searchprofiles":"english"} but it should have different values where the word english is... — Adam, Dec 30 '11 at 03:07

score 7 · Accepted Answer · answered Dec 30 '11 at 03:06

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.

By design, all questions/queries should be known before they are asked. — Xeoncross, Dec 30 '11 at 03:09

score 2 · Answer 2 · answered Dec 30 '11 at 03:08

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']));
}

PHP PDO + Prepare Statement

2 Answers2

Linked