Using PDO with numbers versus strings

Question

I have the following PHP code:

$comp1 = $_POST['cohort_id1'];
$comp2 = $_POST['cohort_id2'];
$comp3 = $_POST['cohort_id1'];
$comp4 = $_POST['cohort_id2'];

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT cat_code, value, :comp1 , :comp2, round(:comp3/:comp4 * 100) as index_number FROM {$table}";
$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY) );
$stmt->execute(array(':comp1' => $comp1, ':comp2' => $comp2, ':comp3' => $comp3, ':comp4' => $comp4 ));
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
header('Content-type: application/json');
echo json_encode($result);

Here is how one of the objects is returned:

?: "national_percent"
cat_code: "edu"
index_number: null
value: "Some College"

What I can't understand is why the first line returns with a "?", but more importantly, why the index_number is null. I suspect it is because those values are being converted into strings, but I'm not sure how to handle that.

To specify the types you bind before execute and do execute() with no params — developerwjk, Mar 04 '15 at 21:05
@developerwjk Not sure I follow this, can you give an example? — jonmrich, Mar 04 '15 at 21:06
See [PDOStatement::bindParam](http://php.net/manual/en/pdostatement.bindparam.php) and [PDOStatement::bindValue](http://php.net/manual/en/pdostatement.bindvalue.php) — developerwjk, Mar 04 '15 at 21:07
so what are your `$comp1` and `$comp2` values? do you have such columns in your table?\ — Alex, Mar 04 '15 at 21:08
Yes. I have these two values as columns in my table. If I run it like this (which is vulnerable to SQL injection), it works fine: `$sql = "SELECT `cat_code`, `value`, `{$comp1}`,`{$comp2}`, round({$comp1}/ {$comp2} * 100) as index_number FROM {$table};";` `$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);` — jonmrich, Mar 04 '15 at 21:11
Because you can't bind columns. `SELECT OK, OK, :comp1 , :comp2` last two, NOT OK. Nor this `round(:comp3/:comp4 * 100)`. Use variables. Here, have a read http://stackoverflow.com/q/182287/ — Funk Forty Niner, Mar 04 '15 at 21:11
@Fred-ii- I'm with you. How do I ensure that I'm not ending up with SQL injection issues since these two variables come from user input? — jonmrich, Mar 04 '15 at 21:15
TBH Jon, I don't know because I'm having trouble wrapping my head around it lol - If you haven't read the manual, have a look http://php.net/pdo.prepared-statements — Funk Forty Niner, Mar 04 '15 at 21:19

score 1 · Accepted Answer · answered Mar 04 '15 at 21:19

To prevent SQL injection of user input that contains column names, check the input against an array of valid column names:

$valid_columns = array('col1', 'col2', 'col3');
if (in_array($user_input_col, $valid_columns)) 
{
  $sql = "SELECT {$user_input_col} from table...";
  ...
}
else
{
     die('Invalid column name');
}

Using PDO with numbers versus strings

1 Answers1