ponydb
SQL injection and truncation attack
Last updated
Was this helpful?
SQL injection and truncation attack
Last updated
Was this helpful?
http://ponydb.chal.uiuc.tf
(note: has unintended element that makes it slightly easier. see miniaturehorsedb for the full-difficulty challenge.)
author: kmh
We are provided with the source code for a Flask web application.
First, a MySQL connection is established and a ponies table is created. The flag is also loaded into the flag variable.
There are two endpoints, GET / and POST /pony. Let's take a look at how they behave.
If a session ID has not yet been assigned, a secure one is generated and the ponies table in the database is populated with a default pony.
Note the unusual format of the favorites data - it is meant to be a JSON string. The default pony has the following favorites data:
A SELECT statement is then used to fetch the ponies corresponding to the session ID. Notice that the favorites data is parsed by json.loads() into a Python dictionary.
The ponies' data is stored into the ponies variable.
Finally, the ponies.html template is rendered with the ponies and flag variables.
In ponies.html, we find that flag is rendered under the condition that the pony's "favorite number" (the one stored in the JSON data) is 1337. This is the condition we have to bypass in order to solve the challenge.
Now, if we look at the POST endpoint, we will start to get an idea of the intended exploit.
There are 7 parameters to submit. Each one is checked for single quotes ('), and the length of the parameters are checked. While we have control over the number parameter, the validation ensures that it is an integer from 0 to 100, so we cannot simply set it to 1337.
If the checks are passed, then the following INSERT statement is executed. Once again, f-strings are used instead of parameterized queries. This time, however, we have control over the variables through the POST request.
While single quotes are filtered, we can easily escape out of the double quotes used in the JSON string. At first thought, we might think that we can simply inject a custom "number": 1337 key-value pair to pass the number check in the Jinja template, thereby rendering the flag in the GET response.
For instance, if we submit number":1337,"color as the favorite_key parameter, the inserted JSON string would be:
Unfortunately, as stated in the documentation, json.loads() handles repeated keys in JSON objects by ignoring everything except the last key-value pair.
Therefore, while we might be able to inject a custom number key-value pair into the JSON string stored in the database, it will eventually be ignored when parsed by the json library.
It took us a few hours to spot this, but there was a flaw in the code. The favorite_key and favorite_value length checks actually don't produce any errors!
The error variable should have been assigned as follows.
Instead, the string was not assigned to any variable.
Now, if we look back at the creation of the ponies table, we will find that the favorites column has a maximum length of 256.
If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.11, “Server SQL Modes”.
Note that while strict SQL mode is enabled by default, the sql_mode option was set to 'NO_BACKSLASH_ESCAPES'.
Let's revisit the earlier payload. We have number":1337,"color as favorite_key, which results in the following favorites string being inserted into the database:
We can leverage the truncation to "push out" the final "number":13 from the 256-character VARCHAR and insert a truncated string without the final key-value pair. This will resolve the repeated key problem when using json.loads().
My final solve script looked like this. We first set the favorite_key and favorite_value, with favorite_key containing the "number": 1337 key-value pair and favorite_value ending in "}. Next, we create the JSON string up to favorite_value.
Then, we can calculate the number of remaining characters needed to complete the 256-character VARCHAR. This number of characters will be appended to the beginning of favorite_value.
The last part of the script just automates the HTTP requests. We could, of course, submit the payload manually as well, and see our pony in all its glory.
Interestingly, f-strings are used instead of . In this case, unfortunately, we do not have control over session['id'].
According to the , if strict SQL mode is not enabled, assigning a VARCHAR value that exceeds the column length will cause the value to be truncated without raising an error.
As specified in the Python MySQL Connector , this option should be a string of comma-separated modes. Evidently, the above configuration leaves out STRICT_TRANS_TABLES and STRICT_ALL_TABLES, either of which would have enabled strict SQL mode.
