Executing multiple SQL queries in one statement with PHP

Question

How to join those multiple queries into one (can I?)

$query = "DELETE FROM aktywne_kody WHERE kodsms ='$kodSMSgracza' AND typkodu ='$id'";
mysql_query($query) or die(mysql_error());

$query = "INSERT INTO uzyte_kody (gracz, kodsms, typkodu) VALUES ('$nickGracza', '$kodSMSgracza', '$id')";
mysql_query($query) or die("Błąd MySQL X04");

$query = "INSERT INTO do_odebrania (gracz, itemDATA, itemQTY) VALUES ('$nickGracza', '$itemDATA', '$itemQTY')";
mysql_query($query) or die("Błąd MySQL X05");

By the way is it better if I do mysql_close($db) after all queries are done?

Make one `$query` and just make sure you add an `;` after each line. — Pitchinnate, Dec 20 '12 at 21:35
Why do you need this? Most of the time readability is more important than little efficiencies. — Muatik, Dec 20 '12 at 21:35
Please don't use `mysql_query` in new applications. It's deprecated and dangerous. At the very least use PDO to ensure your [data is escaped properly](http://bobby-tables.com/php) before you end up in severe trouble because of a SQL injection bug. — tadman, Dec 20 '12 at 21:40
Joining them with `;` gives `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 uzyte_kody (gracz, kodsms, typkodu) VALUES ('UltraMC', '00000000', '' at line 1` — John Smith, Dec 20 '12 at 21:50
To follow on from @tadman about avoiding `mysql_query`, mysqli gives you `multi_query`, which allows precisely this. — TRiG, May 31 '19 at 09:42
@TRiG `multi_query` is also radioactively hazardous and should not be used. It does not support placeholder values and allows injections of multiple statements. — tadman, May 31 '19 at 18:32
@tadman for injections to work you need to fail to properly escape user input. For a newbie or a lazy programmer that can be an issue but for the rest of us there is nothing dangerous about properly escaped user input. — PHP Guru, Oct 15 '20 at 05:59
@PHPGuru Manual escaping can fail because you *forgot*. If you use placeholder values you can't forget, it's effectively impossible. Don't have your code depend on not forgetting. Have it fail safely. Don't have your failure state lead to a potential compromise of your database and possible destruction of your business. Even experienced programmers make tiny mistakes. OpenSSL was famously blown wide open because someone fumbled a simple `if` statement. — tadman, Oct 15 '20 at 06:08
Your advice is good advice in general for coders who otherwise might forget to escape user input. But that doesn't make queries written without prepared statements inherently dangerous. Usually it's pretty easy to tell what needs to be escaped and what doesn't and when user input is lacking proper escaping. — PHP Guru, Oct 15 '20 at 06:19

score 34 · Accepted Answer · edited Dec 05 '15 at 03:12

Pass 65536 to mysql_connect as 5th parameter.

Example:

$conn = mysql_connect('localhost','username','password', true, 65536 /* here! */) 
    or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    INSERT INTO table1 (field1,field2) VALUES(1,2);

    INSERT INTO table2 (field3,field4,field5) VALUES(3,4,5);

    DELETE FROM table3 WHERE field6 = 6;

    UPDATE table4 SET field7 = 7 WHERE field8 = 8;

    INSERT INTO table5
       SELECT t6.field11, t6.field12, t7.field13
       FROM table6 t6
       INNER JOIN table7 t7 ON t7.field9 = t6.field10;

    -- etc
");

When you are working with mysql_fetch_* or mysql_num_rows, or mysql_affected_rows, only the first statement is valid.

For example, the following codes, the first statement is INSERT, you cannot execute mysql_num_rows and mysql_fetch_*. It is okay to use mysql_affected_rows to return how many rows inserted.

$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    INSERT INTO table1 (field1,field2) VALUES(1,2);
    SELECT * FROM table2;
");

Another example, the following codes, the first statement is SELECT, you cannot execute mysql_affected_rows. But you can execute mysql_fetch_assoc to get a key-value pair of row resulted from the first SELECT statement, or you can execute mysql_num_rows to get number of rows based on the first SELECT statement.

$conn = mysql_connect('localhost','username','password', true, 65536) or die("cannot connect");
mysql_select_db('database_name') or die("cannot use database");
mysql_query("
    SELECT * FROM table2;
    INSERT INTO table1 (field1,field2) VALUES(1,2);
");

If anyone wonders why this works - it's not a trick it's just undocumented (in PHP docs) feature of mySQL client. In sources you can easily find `#define CLIENT_MULTI_STATEMENTS 65536 /* Enable/disable multi-stmt support */` which is exactly what OP wanted. — Marcin Orlowski, Dec 22 '12 at 15:59
For code readability; Pass CLIENT_MULTI_STATEMENTS as mysql_connect's 5th parameter. — Jonas Äppelgran, Apr 08 '14 at 08:54
@Husni, Why do you use a "magic number" like 65536 instead of proper constant names? — Pacerier, Mar 11 '15 at 10:32
why not use a "prepare" statement to avoid SQL injection attacks? (Its only a bit of extra code in PHP) — kiwicomb123, Dec 15 '17 at 06:53

score 5 · Answer 2 · answered Jan 06 '16 at 05:18

This may be created sql injection point "SQL Injection Piggy-backed Queries". attackers able to append multiple malicious sql statements. so do not append user inputs directly to the queries.

Security considerations

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.

PHP Doc

score 3 · Answer 3 · answered Apr 08 '14 at 08:59

3

With mysqli you're able to use multiple statements for real using mysqli_multi_query().

Read more on multiple statements in the PHP Docs.

answered Apr 08 '14 at 08:59

Jonas Äppelgran

2,617
26
30

score 0 · Answer 4 · answered Dec 20 '12 at 21:43

You can just add the word JOIN or add a ; after each line(as @pictchubbate said). Better this way because of readability and also you should not meddle DELETE with INSERT; it is easy to go south.

The last question is a matter of debate, but as far as I know yes you should close after a set of queries. This applies mostly to old plain mysql/php and not PDO, mysqli. Things get more complicated(and heated in debates) in these cases.

Finally, I would suggest either using PDO or some other method.

Executing multiple SQL queries in one statement with PHP

4 Answers4

Linked

Related