Sql injection is not working on my site?

Question

I have set up a page and database so I can practice SQL injection. Ive tried some examples and it doesn't seem to work. It seems to add the username and password to that database, even though I input an Injection in the username field. Here is my code:

include "Connection.php";

<form method="post" action="sqltest.php">
<fieldset>
    <label for="username">Username: </label>
    <input type="text" name="username"/>
    <label for="passwd">Password: </label>
    <input type="password" name="passwd"/>
</br>
    <input type="submit" value="Submit" />
    <input type="reset" value="Clear" />
</fieldset>
</form>
            </div>

        </div>



$username = $_POST["username"];
$password = $_POST["passwd"];

if (!empty($_POST)){
if (empty($username) || !isset($username)) {
echo "Please enter a valid username";
   } else if (empty($password) || !isset($password)) {
echo "Please enter a valid password";
} else {
if (strlen($password) >= 6 && strlen($password) <= 8)
    {
        if (is_numeric($password))
        {
            echo "Password cannot be a number<br/>";
        }
        else{
                $encrypted_mypassword=md5($password);
                $query =  "INSERT INTO sqltest (name,password) VALUES ('$username', '$password')";
                $result = mysqli_query($connection,$query);

                echo "You have successfully registered";
        }
    }
    else {
        echo "Password must be between 6 and 8 characters in length<br/>";
    }

 }
}

Can anyone tell me some examples that would drop the table, or insert a user? I just realized that its automatically escaping characters. Surely this doesn't prevent all SQL injection. Is there anything i can inject that will overcome this? Thanks!

Answer 1

In this particular example there's very little you could do, even if you hadn't magic_quotes.

That's because a SQL injection normally can only add to the query string. Since the string is an INSERT command, you can only INSERT something.

In theory, if the SQL server behind supported "chained commands", you could transform a command in two:

INSERT INTO ... VALUES ('...','...'); DROP TABLE Students; -- ');
                               |<--- injected code --------->|

But to do this, the original code must use mysqli_multi_query, not mysqli_query.

One thing you can do is to modify your script to show the query instead of executing it. So you can more easily check what's going on.

And one thing you could try with mysqli (which is why one should use prepared statements and bound parameters) is to play with encoding. Magic quotes will defang single quote marks, but can be thwarted by incorrect (and sometimes correct) UTF8 sequences, or double escapes.

Also, magic quotes is not perfect.

For example, if you tried with

username:      hello\
password:      ,XXX); --

and the query became

INSERT INTO sqltest (name,password) VALUES ('hello\', ',XXX);--')

that would really try and insert

username:      "hello', "
password:      XXX

Now XXX may not contain quotes nor ; . But it could contain a function, such as SLEEP(), which still allows some measure of "attack" against a site (it blocks a SQL connection).

In some circumstances you can build a string by using CONCAT and CHR: instead of 'foo' (contains quotes), you can use CONCAT(CHAR(102),CHAR(111),CHAR(111)) that does not.

It is also possible (but you need to be able to execute arbitrary statements, such as PREPARE and EXECUTE, not just hijacking an INSERT) to build a quoted query and execute it without using quotes:

PREPARE bobby FROM CONCAT(CHAR(68),CHAR(82),CHAR(79),CHAR(80),CHAR(32)...)
EXECUTE bobby; -- D,R,O,P, ,T,A,B,L,E, ,S,t,u,d,e,n,t,s,...

Answer 2

That SQL injection don’t work on your server may be due to magic quotes, which escape certain characters in incoming data with backslash escape sequences:

[…] all ' (single-quote), " (double quote), \ (backslash) and NULL characters are escaped with a backslash automatically. This is identical to what addslashes() does.

These are exactly the characters you would need to escape from the MySQL string literal.

Although this mitigation may work in your example, one should not rely on it as magic quotes may be disabled or even no longer available:

Warning This feature has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 5.4.0.

Instead use known techniques to prevent SQL injections in PHP.