CODEWITHSUNDEEP
phpmysqlpdo
user3011784
user3011784

Reputation: 839

PDO query to mysql with a numeric name for mysql column. How to execute properly

I have a field in mysql table called: "1" which is a number...

MY PDO CODE IS:

$category = 1;

$stmt = $conn->prepare("SELECT * FROM account where :category = '1'");
$stmt->bindParam(':category', $category, PDO::PARAM_INT);
$stmt->execute();

It doesn't work because in mysql I have to use: 

SELECT * FROM account where `1` = '1';

and not:

SELECT * FROM account where 1 = '1';

how can I do that with my PDO ?

thx

Upvotes: 2

Views: 139

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270607

PDO does not permit you to use a bound parameter as a column or table identifier so it is not directly possible to use :category with prepare()/execute() and successfully substitute a numeric column name.

But if you can safely validate the numeric column name from your dropdown menu to prove it is in an acceptable range and is indeed an integer, then it is safe to use a variable in the query. You will need to quote it with backticks as you are already aware.

Validate it with an expression like ctype_digit($category) or preg_match('/^\d+$/', $category) and test that it is in the range of integers for which you have column names.

// Validate $cateogry
// Assuming it came from $_POST['category']
$category = isset($_POST['category']) ? $_POST['category'] : null;

// And assuming the possible range was 1 - 4
// Ensure the input value was an integer with ctype_digit()
if (ctype_digit($category) && $category >= 1 && $category <=4) {
  // Execute the query with a backtick-quoted $category
  // Since you aren't going to bind any params, you can skip the overhead of
  // prepare()/bindParam()/execute() and just call query() instead
  $result = $conn->query("SELECT * FROM account where `$category` = '1' order by rand()");
  if ($result) {
    // Fetch rows and do whatever you planned with them
    $rows = $result->fetchAll(PDO::FETCH_ASSOC);
  }
}
else {
  // Invalid value, don't perform your query, show an error, etc....
}

If you don't have the ctype extension installed (you probably do) then use the preg_match() example instead.

One other possibility would be to use in_array() with range() assuming your columns are named sequentially. I maybe prefer this one to either of the other validation options:

if (in_array($category, range(1, 4))) {
  // $category is valid - go ahead and query with it as above
}

As mentioned in the comment thread, if you are in any position to change this column naming scheme, it would be a good idea to do so. This is just downright confusing. If you are accepting input for the column name though, it still doesn't change the fact that you can't use a PDO parameter for it. You still need to validate it against an array of possible column names.

Upvotes: 1

Related Questions