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
– developerwjk, Commented Mar 4, 2015 at 21:05
@developerwjk Not sure I follow this, can you give an example? — jonmrich
– jonmrich, Commented Mar 4, 2015 at 21:06
so what are your $comp1 and $comp2 values? do you have such columns in your table?\ — Alex
– Alex, Commented Mar 4, 2015 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
– jonmrich, Commented Mar 4, 2015 at 21:11

developerwjk · Accepted Answer · 2015-03-04 21:19:12Z

1

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

answered Mar 4, 2015 at 21:19

developerwjk

8,6342 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using PDO with numbers versus strings

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related