CODEWITHSUNDEEP
phpmysqlpdo
jonmrich
jonmrich

Reputation: 4323

Using PDO with numbers versus strings

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.

Upvotes: 0

Views: 32

Answers (1)

developerwjk
developerwjk

Reputation: 8659

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

Upvotes: 1

Related Questions