Syntax error or access violation: 1064 in code

Question

I am having problems running a PDO execute and returns an error in MySQL syntax.

The code is as follows:

try {
      global $connect;
      $arr = array(':ranked' => $db_rank, ':tier' => $db_tier, ':id' => $_SESSION['user_id']);
      $query = $connect->prepare('UPDATE users SET :ranked = :tier WHERE id = :id');
      $query->execute($arr);
    } catch (PDOException $e) {
      echo $e->getMessage();
    }

where $db_rank returns a string with the column name(conversion from json) and $db_tier returns a joined string(again conversion from json).

It is inside a loop that should update 1-3 columns, but upon execution an exception is thrown:

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 ''<column name1>' = '<value1>' WHERE id = '3'' at line 1
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 ''<column name2>' = '<value2>' WHERE id = '3'' at line 1
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 ''<column name3>' = '<value3>' WHERE id = '3'' at line 1

It should probably be because of the passing of the table column as a variable, in which case how should I proceed to loop it with 3 different pre-set table names without making it spaghetti code ?

Found my answer: Should prepare the statement with " and not with ' so I can use variables. The variables themselve are const and are fetched using a whitelist(upon decoding from the json request). — Mishoka, Nov 25 '18 at 22:19
Not sure what you mean. There are no variables in your query. Please post your answer, or update the question. — user3783243, Nov 25 '18 at 22:20
Check inside $arr how I address $db_rank and $db_tier which are variables from a whitelist function that decodes a json file. Anyways, I found my answer already. — Mishoka, Nov 25 '18 at 22:23
`:db_rank` is incorrect because it is a column, `$db_rank` is not in the query. `$db_tier` is correct because it is the value. Don't see how the quotes make a difference still — user3783243, Nov 25 '18 at 22:25

score 0 · Accepted Answer · answered Nov 25 '18 at 22:21

Found my answer:

Should prepare the statement with " and not with ' because inside the array the type changes 3 times(once from function, once from passing and once from PREPARE statement). The variables themselve are const and are fetched using a whitelist already(upon decoding from the json request).

Syntax error or access violation: 1064 in code

1 Answers1