PHP function returning MySQL syntax error

Question

I don't think there is too much to explain here. The codes are self explanatory.

I have written this function

function checkDuplicate($table, $field, $value){
  global $pdo;
  $stmt = $pdo->prepare("SELECT COUNT(*) as cnt FROM :table WHERE :field = :value");
  $stmt-> bindValue(':table', $table);
  $stmt-> bindValue(':field', $field);
  $stmt-> bindValue(':value', $value);
  $stmt-> execute();
  $f = $stmt->fetch();

  if($f['cnt'] > 0){
    return 1;
  }else{
    return 0;
  }
}

I call it this way

if(checkDuplicate("members", "mem_uname", $uname) == 1){
  echo alert_danger("An account with this username already exists.");
  exit();
}

Calling returns this error

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''members' WHERE 'mem_uname' = 'shrey'' at line 1 in E:\xampp\htdocs\buxhost\includes\functions.php on line 35

What is my mistake here? Why am I getting that error? As far as I have checked multiple times, I did not find anything visibly wrong in my codes.

Tablename cannot be a parameter. – Akina Nov 08 '20 at 15:11 — Akina, Nov 08 '20 at 15:11

kks21199 · Accepted Answer · 2020-11-12T07:14:05.120

0

You can't set the table name as a parameter. You will have to pass it in the prepare statement.

function checkDuplicate($table, $field, $value){
  global $pdo;
  $stmt = $pdo->prepare("SELECT COUNT(*) as cnt FROM `$table` WHERE `$field`= :value");
  $stmt->bindValue(':value', $value);
  $stmt->execute();
  $f = $stmt->fetch();

  if($f['cnt'] > 0){
    return 1;
  }else{
    return 0;
  }
}

Edit: Cannot set field name as parameter.

edited Nov 12 '20 at 07:14

answered Nov 08 '20 at 15:14

kks21199

1,116
2
10
29

I came to know later that also cannot set field name as parameter. Hence, I have edited the answer. – RedStar Entertainment Nov 11 '20 at 16:57
@RedStarEntertainment I didn't notice that while posting my answer. I have accepted your edit. – kks21199 Nov 12 '20 at 07:16
It's alright.. Thanks :) – RedStar Entertainment Nov 13 '20 at 02:35

PHP function returning MySQL syntax error

1 Answers1