0

I have an essay on SQL injection ( what it is - how its done and how can it be avoided ). I get what it is and how it works. But i dont seem to be able to reproduce an injection on my database.

I made a pretty simple database ,using mysql workbench, meant for a video club. movies - stock - price - customers shopping cart etc. I also made a pretty simple html page from which i can add movies - view what i have in stock etc.

So i have a txt field in which i enter a movie name and i get back some info for this specific movie.

The code that gets the name i type and makes the query is ::

$name = $_POST ['txtfld'];
$sql = ("SELECT * FROM test_table WHERE adad = '$Mname'");
if ($result = mysqli_query($dbc,$sql))

Now when i give 'a' as an input everything works as expected. I get back the one entry that has pk equal to [a]. Query becomes :: SELECT * FROM test_table WHERE adad= 'a'.

Next step was to see if i can get the whole table or some random entry from it. Input was : [ a' OR 'x'='x ] Query becomes :: SELECT * FROM test_table where adad = ' a' OR 'x' = 'x ' Everything works as expected and i get back the whole table contents.

Next step was to try inject a second query. I tried to update the test_table. Input was :: [ a;' update test_table set asda = '123456' where adad = 'u ] Query now becomes :: SELECT * FROM test_table WHERE adad= ' a;' UPDATE test_table SET asda ='123456' WHERE adad = 'u '

I got a syntax error so i tried every syntax i could think of including [ a;' UPDATE test_table SET asda = '123456' where adad = 'u';# ] . None of them worked.

Thing is, i dont really get why i get a syntax error.

For the input given above mysqli_error returns this message

error: 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 'update test_table set asda =
'123456' where adad = 'u'' at line 1

while an echo i inserted returns this 

SELECT * FROM test_table WHERE adad = 'a;' UPDATE test_table SET asda = '123456' WHERE 
adad = 'u'

I dont see any syntax error in the echo return and i dont get where the second [ ' ] character in the end of the mysqli_error return, comes from.

From what i understand this is rather a failure in executing a second query ( no matter what the query is - drop, insert, update )

Do i miss something?

Thanks in advance.

Michael.

Silas
  • 125
  • 2
  • 11
  • [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) – John Woo Feb 27 '13 at 16:02
  • In your posted example you have a-semicolon-quote: `' a;' UPDATE`. It should be a-quote-semicolon: `' a'; UPDATE `. Thus SQL can't distinguish the end of the `SELECT` and the start of the `UPDATE`. – Michael Green Jun 05 '14 at 05:06

2 Answers2

2

mysql's PHP driver does NOT allow multiple queries in a single ->query() call, exactly for this reason. It's an anti-injection defense, to prevent the classic Bobby Tables attack. This true for all PHP db interfaces (mysql, mysqli, pdo), as they all use the same underlying mysql C api library to actually talk to the db. Any attempt to run 2+ queries in a single query call results in the syntax error.

Note that it does NOT protect against your ' or 1=1 injection, however.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Okay, thought so. But still Bobby tables attack is a pretty common method as i see ( a lot of sites describing sql injection use this method more or less ). What kind of a db should i have to be able to alter the schema instead of just be able to take info i shouldnt be able to acquire? thanks – Silas Feb 27 '13 at 16:22
  • 2
    can't speak for the other 'big' dbs, but unless they've been specifically hardened for PHP, or have the no-multiple-query business baked into their interface libs, ANY db is theoretically vulnerable to schema alteration. the main problem is that people run their sites using DB accounts that are FAR too privileged. a web-facing account should NEVER have alter/drop privs. select definitely, update/delete MAYBE, alter/drop... **NEVER EVER**. e.g. bobbytables would be impossible with a properly permissioned account. – Marc B Feb 27 '13 at 16:25
  • PDO::query() does allow multi-query by default. – Bill Karwin Feb 27 '13 at 16:31
  • @MarcB. Just managed to protect the database by limiting the permissions. Thank you. – Silas Mar 05 '13 at 15:14
0

In order for your stacked query injection technique to work, you will need to use the "mysqli_multi_query()" function:

http://php.net/manual/en/mysqli.multi-query.php http://www.websec.ca/kb/sql_injection#MySQL_Stacked_Queries

MsSQL is the only database that supports stacked queries by default.

Also possibly a better injection technique, and a more reliable one, would be a UNION attack, and then dump the MySQL credentials from the "mysql.user" table, then use these to compromise the database.

eliteparakeet
  • 739
  • 1
  • 5
  • 14
  • Thank you. Exactly what i wanted to know. Changed my php from mysqli_query to mysqli_multi_query and boom. I can alter my schema using the html forms. I had already used Union attack with success but only to obtain info i "wasnt supposed" to obtain. Not to alter the schema. – Silas Mar 05 '13 at 12:51