MySQL PDO - how to deal with question marks in field names

Question

I've inherited a MySQL database that has field names like the below:

'Profile is Subscribed ?' & 'Private ?'

These can't be changed as the table is used by different systems within the business.

But, I want to use PHP PDO to update these records.

For example: UPDATE Guests SET `Profile is Subscribed ?` = :subscribed, `Private ?` = :private WHERE intID = 123;

But, when using placeholder's I'm getting the "mixed name and positional parameters" error message.

Is there a work around for this (that doesn't involve renaming the fields)? Can I escape the question mark (I've tried "/?", but no difference)?

Thanks

EG `UPDATE Guests SET \`Profile is Subscribed ?\` = :subscribed, \`Private ?\` = :private WHERE intID = 123` — RiggsFolly, Dec 16 '19 at 16:23
**Now you have changed the question** Does it work with backticks or not? — RiggsFolly, Dec 16 '19 at 16:25
You've now updated the code in the question. Does that mean that this isn't a copy/paste of your actual code, but a rewrite for us here? Please don't do that. Always post the _actual_ code. If you want to post what you've tried, add it to the question but keep the original. — M. Eriksson, Dec 16 '19 at 16:26
Do you really need to see the 100+ fields that don't have a question mark in the name? — JezB, Dec 16 '19 at 16:27
It's probably due to using prepared statements, as ? is normally a placeholder for the a bind param. I'll see if I can find anything related. — Nigel Ren, Dec 16 '19 at 16:27
Probably not, but a real and un summerised error message may be of use — RiggsFolly, Dec 16 '19 at 16:27
Well, since we have no idea what your issue is, we need to see all relevant code or we can only guess. What if you have a typo somewhere in the query, which you then remove for us here? Also, rewriting and removing some parts are two different things. In this case, you posted an invalid query (using single quotes instead of back ticks) which kind of proves my point. — M. Eriksson, Dec 16 '19 at 16:28
*"Do you really need to see the 100+ fields that don't have a question mark in the name?"* - A minimal working example would help. Check your db's collation also. — Funk Forty Niner, Dec 16 '19 at 16:29
You might also want to check how you're connecting/querying. It could have something to do with UTF-8, hard to say. I am picking at straws here. — Funk Forty Niner, Dec 16 '19 at 16:30
The query above works when values are added & it's entered directly into MySQL. — JezB, Dec 16 '19 at 16:31
*"The query above works when values are added & it's entered directly into MySQL."* - So what's the question for then? You saying that if empty fields are left, that it doesn't work? Your question is very unclear. — Funk Forty Niner, Dec 16 '19 at 16:31
PDO is picking up the question mark as a parameter when it's not. — JezB, Dec 16 '19 at 16:33
I'm sorry, but I for one cannot keep on guessing. Please post more code and the db's settings and how you're connecting. I will now have to opt out of this one. I wish you well with this. — Funk Forty Niner, Dec 16 '19 at 16:34

Paul Spiegel · Accepted Answer · 2019-12-16T17:44:39.500

This seems to be a bug in PDO. If I try

$stmt = $pdo->prepare('
    UPDATE Guests SET
      `Profile is Subscribed ?` = :subscribed,
      `Private ?` = :private
    WHERE intID = 123
');
$stmt->execute(['subscribed' => 1, 'private' => 0]);

I get this error:

PDOException: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters in ...

As a workaround: Don't use named placeholders. Use ? instead. But then you need to bind the values in correct order:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare('
    UPDATE Guests SET 
      `Profile is Subscribed ?` = ?,
       `Private ?` = ?
     WHERE intID = 123
');
$stmt->execute([1,0]);

It's important, that you set ATTR_EMULATE_PREPARES to false. Otherwise you will get another error:

PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in ...

Another workaround is to use the SQL mode ANSI_QUOTES:

$pdo->exec("SET SESSION sql_mode = concat(@@sql_mode, ',ANSI_QUOTES')");
$stmt = $pdo->prepare('
    UPDATE Guests SET
      "Profile is Subscribed ?" = :subscribed,
      "Private ?" = :private 
    WHERE intID = 123
');
$stmt->execute(['subscribed' => 1, 'private' => 0]);

But personally - I would rather rename the column names.

Agreed on renaming the columns. It's needlessly risky to use punctuation in column names. You don't do that in variable names of any programming language. — Bill Karwin, Dec 16 '19 at 18:29
Thank you Paul - SQL mode ANSI_QUOTES was the fix I needed in this instance. I agree, I would NEVER have column names like this, but in this case, I can't change them! Thank you again. — JezB, Dec 17 '19 at 08:17
@BillKarwin - I've seen something like `${'Profile is Subscribed ?'} = ...;` in PHP :-) — Paul Spiegel, Dec 17 '19 at 15:59

MySQL PDO - how to deal with question marks in field names

1 Answers1