Error in MySQL syntax when i try to create database and set user privileges through PHP

Question

This is the first time that i will ask a question in this forum.

I am working on a php project in which i have to -through php- create a database and insert some empty tables.

The issue here is that i always get an error message regarding syntax when i try to add privileges to the superuser (for some reason, if i don't, an error message states that i don't have permition to access the database).

Here is the code for creating the database and assigning privileges:

$sql = "CREATE DATABASE IF NOT EXISTS `database` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; "; 

$sql .= "GRANT SELECT database.* TO 'superuser'@'localhost' ";

$sql .= "GRANT ALL ON database." TO 'superuser'@'localhost' IDENTIFIED BY 'password'; ";

$sql .= "FLUSH PRIVILEGES";

Can you tell me what did wrong? Thanks in advance!

Most database libraries allow only one query at a time

Pekka
– Pekka

2013-11-09 17:14:04 +00:00
Commented Nov 9, 2013 at 17:14 — Pekka
– Pekka, Commented Nov 9, 2013 at 17:14

doublesharp · Accepted Answer · 2013-11-09 17:19:26Z

1

You need to end each of your SQL statements with a semi colon ;, it is missing after the first GRANT. The second GRANT uses database." and should be database.*, should also be surrounded by backticks as "database" is a reserved word. That said, the first GRANT can be omitted as the it will be included with GRANT ALL.

$sql = "CREATE DATABASE IF NOT EXISTS `database` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; ";
$sql .= "GRANT ALL ON `database`.* TO 'superuser'@'localhost' IDENTIFIED BY 'password'; ";
$sql .= "FLUSH PRIVILEGES;";

You can also use a HEREDOC format to make it easier to read

$sql = <<<SQL
  CREATE DATABASE IF NOT EXISTS `database` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
  GRANT ALL ON `database`.* TO 'superuser'@'localhost' IDENTIFIED BY 'password';
  FLUSH PRIVILEGES; 
SQL;

edited Nov 9, 2013 at 17:19

answered Nov 9, 2013 at 17:13

doublesharp

27.9k6 gold badges56 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

doublesharp Over a year ago

There is one missing after your first GRANT statement

CrikoC Over a year ago

when i add the semicolon, i get an error message tha the query has failed

doublesharp Over a year ago

Please see my last edits, there were actually several errors in your example.

CrikoC Over a year ago

$sql = <<<SQL CREATE DATABASE IF NOT EXISTS 'strongim_$this->title' DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; GRANT ALL ON 'strongim_pmsimple_$this->title'.* TO 'DB_USER'@'DB_SERVER' IDENTIFIED BY 'DB_PASS'; FLUSH PRIVILEGES; SQL;

CrikoC Over a year ago

DB_USER, DB_SERVER and DB_PASS are defined variables

|

Collectives™ on Stack Overflow

Error in MySQL syntax when i try to create database and set user privileges through PHP

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related