mySQL inserts failing in PHP but not in phpmyadmin

Question

For some reason I've got a few SQL inserts that fail when being executed via php, but not when pasted directly into phpmyadmin.

Here is the PHP code:

private function createMockAccount($email){
    $passwordHash=$this->nonce();
    $query='insert into GeneralUser(email,password,isTemp) values("'.$email.'","'.$passwordHash.'",1);';
    $query=$query.'insert into ContentUser(email) values("'.$email.'");';
    error_log("executing the following query to create mock accounts: ".$query);
    $database=mysqli_connect(host,username,password,dbName);
    if (mysqli_connect_errno()) {
         error_log("Connect failed: ".mysqli_connect_error());
    }
    if(!mysqli_query($database,$query)){
     error_log("Errormessage:".mysqli_error($database));    
    }
    return $passwordHash;
}

When executed, the following is in the error log:

[11-Aug-2013 04:20:42 America/Denver] executing the following query to create mock accounts: insert into GeneralUser(email,password,isTemp) values("suxhfisk@guerrillamail.biz","D0Obkg/Lue+4AFSAzinqdo/XAAwDhMfitmnm53R0RwA=",1);
        insert into ContentUser(email) values("suxhfisk@guerrillamail.biz");
[11-Aug-2013 04:20:42 America/Denver] Errormessage: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 'insert into ContentUser(email) values("suxhfisk@guerrillamail.biz")' at line 2

I also tried using mysqli_real_escape_string the following way to no avail:

$query=mysqli_real_escape_string($database,$query);

With that the error_log still shows the same error...

[11-Aug-2013 04:51:42 America/Denver] executing the following query to create mock accounts: insert into GeneralUser(email,password,isTemp) values("suxhfisk@guerrillamail.de","guR8Sps8e4Iv1LBXmsREH2GVd+WH/cH1Nx/zy9VOnLE=",1);
        insert into ContentUser(email) values("suxhfisk@guerrillamail.de");
[11-Aug-2013 04:51:42 America/Denver] Errormessage: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 '\"suxhfisk@guerrillamail.de\",\"guR8Sps8e4Iv1LBXmsREH2GVd+WH/cH1Nx/zy9VOnLE=\",1' at line 1

I'm sure there is something silly I'm missing, however in both cases, when the string from the error_log is copied directly into phpmyadmin it gets executed successfully.

Any help would be greatly appreciated, thanks! :)

score 0 · Accepted Answer · edited May 23 '17 at 11:56

This is not a query you are running but set of queries.
So, just run them one by one, not in one statement.

Also, you idea of using mysqli_real_escape_string is quite wrong. This function have to be used to escape special characters in string literals only, not to process whole query.

Anyway, you have to use prepared statements instead. As its usage is quite complex with raw mysqi, consider to use PDO instead.

score -1 · Answer 2 · edited May 23 '17 at 10:25

The phpmyadmin query window handles each new line (thus after the ';') token as one task. By this, it is able to run multiple queries at once. If you want to accomplish the same in PHP, you have to use transactions. Here below is an example,

try {
    // start transaction
    $db->beginTransaction();
    // do 1st query 
    $db->query("insert into GeneralUser(email,password,isTemp) values('" . $email . "', '" . $passwordHash . "',1);");
    // do 2nd query
    $db->query("insert into ContentUser(email) values('" . $email . "');");
    // commits the operation
    $db->commit();
}
catch(Exception $e) {
    // either echo or log the error message
    // rollback the above successful query (optional, but depends of your design)
    $db->rollback();
}

Or use PDO instead.

As was answered by 'your-common-sense' and by you, that was indeed the case, thanks a lot! can't believe I missed that :) — user2672102, Aug 11 '13 at 15:19

mySQL inserts failing in PHP but not in phpmyadmin

2 Answers2