0

I am trying to create a prepared statement using PDO that will allow me to create mysql users using data collected from a form. When i run the command though, i get the error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''select' ON 'testjoke.authors' TO 'corey'@'localhost'' at line 1

I am currently just using data from some variables i created for testing, instead of data from the form. The code looks like this:

$grantQuery = $db->prepare("GRANT ? ON ? TO ?@'localhost';");

    $select = 'select';
    $testjoke = 'testjoke.authors';
    $pdoemail = 'corey';

    $grantQuery ->execute(array(
                                $select,
                                $testjoke,
                    $pdoemail
                ));

I have enabled Mysql general logging, and the query never even shows up on it (never executed).

I have been trying to find a fix for this for a couple of days now, but i am having no luck.

Any help would be greatly appreciated.

Thanks Corey

Fishingfon
  • 1,034
  • 3
  • 19
  • 33
  • possible duplicate of [Reference - frequently asked questions about PDO](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo). See this answer in particular ~ [Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?](http://stackoverflow.com/questions/15990857/reference-frequently-asked-questions-about-pdo#15991422) – Phil May 12 '14 at 01:26

1 Answers1

2

Just as you cannot write SELECT ? FROM ?, neither can you divide GRANT into a prepared query.

Prepared query values can only be values. For safety reasons among many others, you cannot pass in keywords. The only parameter that might work in your case is the username, as that is a string.

PDO is trying to run:

GRANT 'select' ON 'testjoke.authors' TO 'cory':'localhost';

You can see why this won't work, I hope.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • Hi, Ok thanks, i wasnt aware that you couldnt pass keywords into PDO (i am quite new to mysql/php). Thanks again, Cheers, Corey – Fishingfon May 12 '14 at 01:26
  • 1
    Things like this are part of the reason why I was so adamant about sticking with the outdated `mysql_` functions, but ultimately you shouldn't *need* variable tables/columns and suchlike. – Niet the Dark Absol May 12 '14 at 01:26