MYSQL Syntax error in php but sql is valid

Question

I'm trying to start a transaction is mysql and insert data into the database. The database source sql can be found on github here. Here is the error:

Error: START TRANSACTION; INSERT INTO Books(Title, PublicationDate, PurchaseDate, Description, LocationID, GenreID) VALUES('Simple Genius', '2008-4-1','2009-5-7','','Hardbook Library','Fiction'); SET @bookid = LAST_INSERT_ID(); INSERT INTO BookAuthors(FirstName, MiddleName, LastName) VALUES('David', '', 'Baldacci'); SET @authorid = LAST_INSERT_ID(); INSERT INTO AuthorsInBooks(AuthorID, BookID) VALUES(@authorid, @bookid); COMMIT; 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 Books(Title, PublicationDate, PurchaseDate, Description, LocationID,' at line 3

Near 'INSERT INTO Books(Title, PublicationDate, PurchaseDate, Description, LocationID,' doesn't make sense to me because it is missing GenreID after LocationID. Am i missing something? When I copy and paste this code into phpmyadmin it works fine. My php version is 5.4.

Here is php code:

$sql = "
START TRANSACTION;

INSERT INTO Books(Title, PublicationDate, PurchaseDate, Description, LocationID, GenreID)
VALUES('".$Title."', '".$YearWritten."','".$YearPurchased."','".$Description."','".$Location."','".$Genre."');

SET @bookid =  LAST_INSERT_ID();

INSERT INTO BookAuthors(FirstName, MiddleName, LastName)
VALUES('".$AuthFirstName."', '".$AuthMiddleName."', '".$AuthLastName."');

SET @authorid =  LAST_INSERT_ID();

INSERT INTO AuthorsInBooks(AuthorID, BookID)
VALUES(@authorid, @bookid);

COMMIT;
";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);

Please read http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — user2864740, Jan 15 '16 at 18:38
I was told to use this. Is this not effective enough? @user2864740 mysqli_real_escape_string — Jacob Wilson, Jan 15 '16 at 19:00
@JacobWilson: You may want to read http://stackoverflow.com/q/5741187, which is equally valid for mysqli. — eggyal, Jan 15 '16 at 19:07

score 2 · Accepted Answer · answered Jan 15 '16 at 18:23

2

mysqli_query() can only execute 1 query, if you want to execute multiple queries, you need:

if (mysqli_multi_query($conn, $sql)) {

answered Jan 15 '16 at 18:23

jeroen

91,079
21
114
132

1

But rather than doing that, just use API calls to manage transactions and send each command separately... and, of course, parameterise those variables! – eggyal Jan 15 '16 at 18:24
1

Worked perfectly. Thank you so much! – Jacob Wilson Jan 15 '16 at 18:26
@eggyal I completely agree. – jeroen Jan 15 '16 at 18:27
Can I see an example of what you mean @eggyal ? – Jacob Wilson Jan 15 '16 at 18:28
The @jeroen answer is correct, if you need run multiple queries use "mysqli_multi_query" or PDO. Here other answer related to this theme: http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd – Joseph Collins Jan 15 '16 at 18:44
1

Care to explain @user2864740 – Jacob Wilson Jan 15 '16 at 18:56
[Multiple Statements, Security Considerations](https://goo.gl/ExrKT2)—The API functions `mysqli_query()` and `mysqli_real_query()` do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as *; DROP DATABASE mysql* or *; SELECT SLEEP(999)*. If the attacker succeeds in adding SQL to the statement string but *mysqli_multi_query* is not used, the server will not execute the second, injected and malicious SQL statement. – eggyal Jan 15 '16 at 19:04

score 2 · Answer 2 · answered Jan 15 '16 at 18:50

In response to your comment "Can I see an example of what you mean @eggyal ?":

// mysqli provides API calls for managing transactions
mysqli_autocommit($conn, false);

// parameterise variables - NEVER concatenate them into dynamic SQL
$insert_book = mysqli_prepare($conn, '
  INSERT INTO Books
    (Title, PublicationDate, PurchaseDate, Description, LocationID, GenreID)
  VALUES
    (?, ?, ?, ?, ?, ?)
');

// bind the variables that (will) hold the actual values
mysqli_stmt_bind_param(
  $insert_book,
  'siisss', // string, integer, integer, string, string, string
  $Title, $YearWritten, $YearPurchased, $Description, $Location, $Genre
);

// execute the statement (you can change the values of some variables and
// execute repeatedly without repreparing, if so desired - much faster)
mysqli_stmt_execute($insert_book);

// mysqli provides API calls for obtaining generated ids of inserted records
$book_id = mysqli_insert_id($conn);

// ... etc ...

// use the API call to commit your transaction
mysqli_commit($conn);

// tidy up
mysqli_stmt_close($insert_book);

Note that I've not included above any error detection/handling, which you'd certainly want to include in any real-world code.

MYSQL Syntax error in php but sql is valid

2 Answers2