SQL injection with "INSERT" statement

Question

I am currently studying the most basic SQL injections that are possible. Pure and solely for the purpose of teaching myself the basics before advancing to more advanced ones. I am recreating this situation with the following statement:

$result = $dbConnect->query("INSERT INTO UserTable VALUES (NULL,'$name','$email', '$userName', '1')");

I have a few questions regarding this statement (I know this is bad practice but I want to start as basic possible).

1. Is it possible to inject something into this even if it's in an if statement?
2. How can you escape out of this query?
3. Is there a possibility to echo the whole statement so you could get more intel in the php used, meaning that the whole result query is being echo'ed due to the injected code?
4. If this has a potential SQL injection situation. Are there any people dumb enough to code this way?

Thanks in advance!

Answer 1

Is it possible to inject something into this even if it's in an if statement?

Yes. With multiline queries, a simple '; drop table usertable;-- works. With single line queries, ' (select abc from def),0)-- can work, though with some more nesting you can run pretty much anything.

How can you escape out of this query?

Simply escaping everything won't work, as this may change the meaning of some of the characters. You have to be careful.

Stuff like mysqli_real_escape_string() are also vulnerable. Use prepared statements.

Is there a possibility to echo the whole statement so you could get more intel in the php used, meaning that the whole result query is being echo'ed due to the injected code?

Sure, store the string first and then echo it. But that may be worse, it's best if the attacker can't see anything. It's preferable to log the queries.

If this has a potential SQL injection situation. Are there any people dumb enough to code this way?

It's not so much dumbness as cluelessness/carelessness, but yes. MySQL injection is one of the most common web vulnerabilities out there.

Answer 2

1) The SQL query simply being wrapped in a PHP if statement will have no impact upon whether or not an attacker can inject into the statement.

2) Can who escape out of it? You or the attacker? Assuming you mean the attacker, and given the example you provide, no one can. The values you have used for the query are hard coded. For there the be a SQL injection vulnerability you have to use user input. For example the email input field for the query could be used to feed something other than [email protected] into the statement.

3) If you mean is it possible for the attacker to have that output to their browser, then possibly. It depends on your level of input sanitisation and your level of error output. If an attacker feeds invalid input to the query to cause a syntax error and you have a fairly verbose error output then yes, it is possible. Amongst other options anyway.

4) There are always people dumb enough to code this way. 99.9% of the tutorials I have seen on the Internet document how to use the mysql interface for starters which has now been deprecated. Secondly most of them don't even talk about security and just simply show you how to get a working block of code with the least effort.

Answer 3

The first thing, you should check here - is the support for multiple statements in a single query. You can leave INSERT statement and try to execute another SQL-query: $userName = x', 1); DROP TABLE userTable;#

However, let's assume, that multiple statements are disabled (for example, old mysql_query doesn't support it by default). The second idea to check your PHP-code would be - using subqueries. Instead of inserting some constant-strings (like $name, $email, $username, etc.), we could try to insert the value of subquery:

$email = x',(SELECT 'aaa' FROM DUAL),1)#.

This payload would result in query: INSERT INTO UserTable VALUES (NULL,'$name','x',(SELECT 'aaa' FROM DUAL),1)#', '$userName', '1')

So as you see, in userName column (from UserTable) you will see the result of SELECT subquery. If the value of userName is displayed on the victim's website (which I guess, it is) - attacker could check the result of inserted subquery.

Moreover, if attacker wants to be more undetected (creating a lot of entries to the database could be easily noticed) - he could use blind-sqli or error-based techniques in inserted subqueries (you can find more details here).

Answer 4

The point is, by using values which included single quotes in the variable fields, attackers could try to do all manner of bad things to your database, none of which would be what you intended to happen. It probably would only generate a lot of unexpected errors; but whatever it did, it wouldn't be to your benefit.