Using mysqli_multi_query is critical for successful SQL injection

Question

I'm trying to create a SQL injection example, but MySQL rejects the second query every time unless I change it to mysqli_multi_query.

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post" name="dummyDorm">
        <label>Name: </label>
        <input type="text" name="name"><br>
        <input type="submit" value="Submit">
    </form>

    <?php 
        if($_SERVER["REQUEST_METHOD"] == "POST")
        {
            $name = $_POST['name'];
            $conn = mysqli_connect("localhost", "root", "", "testDB");
            if (mysqli_connect_errno()){
                echo "failed to connect" . mysqli_connect_error();
                die();
            }
            else
            {

                $sql = "SELECT * FROM users WHERE name = '{$name}'";
                var_dump($sql);
                if (mysqli_query($conn, $sql))
                {
                    echo "Success";
                } 
                else {
                    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
                }

                mysqli_close($conn);
            }

        }
    ?>

This form doesn't actually do anything, I just want to demonstrate that data can be edited using SQL injection. I want to perform the injection where "DROP TABLE testTable" is entered. My MySQL looks like this:

DROP DATABASE IF EXISTS testDB;
CREATE DATABASE testDB;
USE testDB;

CREATE TABLE users
(
    userID INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
)engine=innodb;

CREATE TABLE testTable
(
    test VARCHAR(10)
)engine=innodb;

INSERT INTO users VALUES(null, "user01");

When entering injection: ';Delete table test table --

The output is as follows: Error: SELECT * FROM users WHERE name = ''; Delete table test table --' There is an error in your SQL syntax; check the manual for your MySQL server version for the correct syntax to use near line 1 "DROP TABLE testTable --"

I continued through the tutorials and they used MySQLi_query and it worked fine, but I had to change it to Mysqli_multi_query to complete the injection.

Thanks

Answer 1

Indeedmysqli_query()Multiple queries are not allowed. To do this, you need mysqli_multi_query(), and if you avoid using that function, you are protected from SQL injection attacks that rely on running multiple queries, such as the DROP TABLE example.

However, in addition to DROP TABLE, SQL injection can also cause other types of harm.

I can access the URL of your PHP page using additional parameters that allow me to control the logic of your SQL query:

Read all rows in the table:

?name=x' OR 'x'='x

Read another table:

?name=x' UNION ALL SELECT ... FROM othertable WHERE 'x'='x

Perform a denial of service attack that kills your database server with a trillion-row temporary table:

?name IN (SELECT 1 FROM othertable CROSS JOIN othertable CROSS JOIN othertable CROSS JOIN othertable CROSS JOIN othertable CROSS JOIN othertable CROSS JOIN othertable) AND 'x'='x

SQL injection is not necessarily a malicious attack. This may be an unintentional use of a legal string, resulting in unexpected SQL syntax:

?name=O'Reilly

The fix for SQL injection is well known and simple: Use parameterized queries.

Check out the mysqli_prepare() manual or the popular Stack Overflow question for a complete example, such as How to prevent SQL injection in PHP?