Before and After: Making Multiple Queries ~ Tech Blog

Wednesday, 11 July 2018

Before and After: Making Multiple Queries

In PHP 4, when you need to make more than one query in a row, you're forced to call mysql_query( ) once for each query. This often happens when you need distinct pieces of information from MySQL or when you want to create a fresh set of tables.

This isn't a big problem when you know your queries ahead of time, because it's easy to loop through and send them to MySQL one at a time. However, for some situations, this is not the case. For example, you're writing a PHP frontend to MySQL, such as phpMyAdmin(http://www.phpmyadmin.net), and want the ability to take a database dump and recreate the information.

Without the ability to send the entire dump at once, you're required to parse the data into individual statements. That's not as easy as it sounds, because you can't just split on the semicolon (;). For example, it's perfectly valid to have a line like INSERT INTO users VALUES('rasmus', 'pass;word');. Since pass;word is inside single quotes, MySQL knows it doesn't signal the end of a statement, but a simple split isn't smart enough to understand this. As a result, you're effectively forced to write a MySQL parser in PHP.

The restriction of one query per request is lifted in MySQLi. This actually wasn't a limitation in PHP, but a deficit in the protocol used by earlier versions of MySQL. Another benefit of MySQL 4.1's updated protocol is the ability to introduce a mysqli_multi_query( ) function for these types of cases.

While this is good news, it also introduces a greater potential for SQL injection attacks. An SQL injection attack is when a malicious person passes in data that alters your SQL so that you end up issuing a different (and probably more dangerous) SQL query than what you expected.

Here's an example that's supposed to return the bank account information for only a single user:

$sql = "SELECT account_number, balance FROM bank_accounts 

        WHERE secret_code LIKE '$_GET[secret_code]'";

You know that it's important to use a secret code so that people can't access other people's information by incrementing a primary key ID number, because those numbers are easy to guess. However, you've forgotten to call mysql_real_escape_string( ) on $_GET['secret_code'], and this is trouble. A cracker can set secret_code to ' OR '1' LIKE '1, and that turns your query into:

SELECT account_number, balance FROM bank_accounts 

 WHERE secret_code LIKE '' OR '1' LIKE '1';

Since any string always matches itself, this effectively alters the query to return all rows. If there's any positive to this exploit, it's that since MySQL only allows you to execute a single query at a time, you cannot radically alter the query.

However, now that you can issue multiple queries, you're vulnerable to something even worse. People can issue any query they want:

SELECT account_number, balance FROM bank_accounts 

 WHERE secret_code LIKE '';

UPDATE bank_accounts 

   SET balance = 1000000

 WHERE account_number = '12345678';

By altering secret_code to '; UPDATE bank_accounts SET balance = 1000000 WHERE account_number = '12345678';, you terminate the first query and append another. The second query isn't limited by the constraints of the first, so a query that's supposed to SELECT data now also UPDATEs it (or data in any other table).

To prevent this security vulnerability, mysqli forcibly disables mysqli_query( )'s ability to make multiple queries. Instead, there's an alternate set of functions for you to use.

3.8.1 mysql: Making Multiple Queries

In mysql, the best way to handle multiple queries is to place them in individual array elements. You can loop then through the array and issue the queries.

Example 3-9 creates a users table and populates it with rasmus and zeev accounts.

Example 3-9. Executing multiple queries with mysql_query( )

$queries = array(

    "DROP TABLE IF EXISTS users;",

    "CREATE TABLE users(username VARCHAR(50) UNIQUE,

                        password VARCHAR(50));",

    "INSERT INTO users VALUES('rasmus', 'z.8cMpdFbNAPw'), 

                             ('zeev',   'asd34.23NNDeq');",

    "SELECT * FROM users WHERE username LIKE 'rasmus';");

    

foreach ($queries as $query) {

    if ($result = mysql_query($query, $db) and $result !=  = true) {

        while ($row = mysql_fetch_row($result)) {

            print "$row[0]\n";

         }

    }

}

It's important to quote the SQL statements properly because they often have embedded single and double quotation marks. In this case, the INSERT andSELECT statements both have single quotes.

The foreach iterates through each element and calls mysql_query( ). If everything goes okay, the function returns a true value. When there are rows to process, such as in a SELECT or DESCRIBE query, mysql_query( ) returns a resource. When the request was successful but there's no additional information, it just returns true.

This allows you to distinguish between queries where you can call mysql_fetch_row( ) by placing and $result != = true inside the conditional. You must use both and instead of && and != = instead of !=; otherwise, you get different results than expected because of operator precedence and type coercion issues.

Finally, the while loop retrieves each row and prints out the first column. Since only one of the four statements made in this example returns results and it returns only a single row, the entire output of this loop is rasmus.

Of course, as said earlier, none of this is necessary if your SQL isn't separated into individual statements.

3.8.2 mysqli: Making Multiple Queries

The mysqli extension solves many of the problems incurred when making multiple queries. Most importantly, you don't need to break the SQL apart. This not only eliminates the need for a MySQL parser, but also allows you to more easily place SQL inside your script without worrying about quotation marks.

The downside, however, is that the code to process a request is significantly more complex. Instead of a single function, mysql_query( ), four different functions are necessary to navigate through the results.

Use mysqli_multi_query( ) to send the query itself and mysqli_store_result( ) to load the results of each successive query into a position where they can be fetched. When you're done with a query, free it with mysqli_free_result( ) and move onto the next one using mysqli_next_result( ).

Example 3-10 is a revised version of Example 3-9 showing how these functions work in combination.

Example 3-10. Executing multiple queries with mysqli_multi_query( )

$query = <<<_SQL_

DROP TABLE IF EXISTS users;

CREATE TABLE users(username VARCHAR(50),

                   password VARCHAR(50));

INSERT INTO users VALUES('rasmus', 'z.8cMpdFbNAPw'), 

                        ('zeev',   'asd34.23NNDeq');

SELECT * FROM users WHERE username LIKE 'rasmus';

_SQL_;



if (mysqli_multi_query($db, $query)) {

    do {

        if ($result = mysqli_store_result($db)) {

            while ($row = mysqli_fetch_row($result)) {

                print "$row[0]\n";

            }

            mysqli_free_result($result);

        }

    } while (mysqli_next_result($db));

}

Your SQL is no longer stored in an array, but as a string. This lets you use a heredoc to avoid the problem of single and double quotes.

The mysqli_multi_query( ) function takes a database link and a query, just like mysqli_query( ). If anything goes wrong, such as a parse error, it returns false; otherwise, it places the first result set into position for retrieval and returns true.

Since mysqli_multi_query( ) has already handled the first result, use a do/while loop instead of a while to iterate through all the data. Otherwise, the first call to mysqli_next_result( ) will actually load the second result.

Inside the loop, call mysqli_store_result( ) to transfer each result set to PHP. If the query doesn't return any rows, then mysqli_store_result( )returns false. This actually happens for the first three queries in this example?the DROP, CREATE, and INSERT. Only the SELECT statement causes a non-false response.

This behavior is different from mysql_query( ) and is actually more useful because it allows you to eliminate the and $result != = true check from the code.

Now you can use the standard set of retrieval functions, such as mysqli_fetch_row( ), to process the data. This example just prints out the first column from every row retrieved.

Be sure to call mysqli_free_result( ) to release the memory when you're done with the result. Otherwise, it will pile up until the script finishes running. Since it's easy to make numerous queries using mysqli_multi_query( ), it's easy to quickly use up lots of RAM.

To check if there are additional results without advancing the counter by calling mysqli_next_result( ), call mysqli_more_results( ):

if (mysqli_multi_query($db, $query)) {

    do {

        if ($result = mysqli_store_result($db)) {

            while ($row = mysqli_fetch_row($result)) {

                print "$row[0]\n";

            }

            mysqli_free_result($result);



            // Add divider after all results except the last

            if (mysqli_more_results($db)) {

                print str_repeat('-', 10) . "\n";

            }

        }



    } while (mysqli_next_result($db));

}

The mysqli_more_results( ) function returns the same value as the next call to mysqli_next_result( ). This lets you add dividers or perform some other kind of processing after all statement results except the final one.

If you're put off by the complexity of this code, there's an alternative that combines the benefits of multiple queries with the simplicity of the earlier syntax. The Section 6.3 contains a MySQL multi-query iterator that encapsulates all the logic into a reusable class and lets you foreach through the results
.

Tech Blog

Wednesday, 11 July 2018