SQL injection won't work without using mysqli_multi_query

Question

I'm trying to create an example of SQL injection but MySQL is refusing 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);
            }

        }
    ?>

The form doesn't actually do anything I just want to demonstrate that using SQL injection you can edit data. I want to perform an injection where you enter "DROP TABLE testTable". 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 the injection: '; DROP TABLE testTable --

This is outputted: Error: SELECT * FROM users WHERE name = ''; DROP TABLE testTable --' 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 'DROP TABLE testTable --'' at line 1

I keep going through tutorials and they use MySQLi_query and it works fine but I have to change it to Mysqli_multi_query in order for the injection to go through.

Thanks

Answer 1

It's true that mysqli_query() does not allow multi-query. For that you need mysqli_multi_query(), and if you refrain from using that function, you're safe from SQL injection attacks that rely on running multiple queries, like your DROP TABLE example.

But SQL injection can result in other types of mischief besides DROP TABLE.

I could access the URL of your PHP page with other 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, killing your database server with a trillion-row temp 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 doesn't have to be a malicious attack. It might be an innocent use of a legitimate string that results in unintended SQL syntax:

?name=O'Reilly

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

See full examples in the manual for mysqli_prepare() or in popular Stack Overflow questions like How can I prevent SQL injection in PHP?