Sql Injection Error Based Mysql

[Curtis Cassel]

Thevulnerability is SQL Injection. Error-Based is just a method to make the error message Show Desired Data versus just error, for when we have a blind vulnerability that shows error, so we can extract sensitive data from the database directly (instead of one bit at a time).

Okay, now lets get to the logic. concat(DESIRED_DATA,floor(rand()*2)) will give you different results based on each row of the database result, because that's how rand() works. It gives one random number between 0 and 1 for every row of output. Now since our random is confined to 0,1, it only generates two values. If these two values are different (i.e., 0 and 1) then the query runs fine, and gives you the output you saw above.

However, if they both give the same number, then the group by will fail, because the group_key which was expected to be unique (it's the ID of the temporary result table) will be duplicate, and cause the database to err.

Now, Mysql likes to tell you what exactly is the duplicate value in that query, so it shows you the duplicate key, which will be the DESIRED_DATA concatenated by 1 or 0 (hence the 0x31 to separate them more nicely).

So the two trick parts, count(*) and rand() are both needed so that MySQL actually runs this multiple times, instead of just optimizing past it. But the trick is to create a temporary table that has duplicate keys, and let MySQL tell us about the value of that key in an error.

As you can see in the examples, each request is made to trigger an error. This error will contain the value wanted by the attacker. For example, the mysql version will be retrieved with the error "Duplicate entry 'MySQL version here' for key 1".

Error based SQL injection is useful when you have a page that runs a query where the output is not shown, but will display a database error if there is one. While you could also exploit this using blind SQLi the error based on offers a significant speed increase.

There are lot of excellent SQL injection cheat sheets out there; however, I found the majority provide only the components of a SQL injection rather an entire, working string. As a result, successfully putting a valid query together can take some trial and error and waste precious time. I have thus attempted to create a list of pre-made strings for each type of SQL injection so that they can simply be pasted in with little modification.

As SQL injections can loosely be grouped into three categories, union based, error based (XPath and double query) and inferential (time based and boolean), I have listed them as such. Below you will find MySQL specific syntax whilst I will post my MSSQL cheat sheet shortly.

UNION is used to append our SQL injection to a legitimate query and combine the information we wish to retrieve with that of the legitimate query. Note that you need to enumerate the number of columns first, this can be achieved by using the ORDER BY function or using UNION with NULL values.

The ExtractValue() function generates a SQL error when it is unable to parse the XML data passed to it. Fortunately, the XML data, and, in our case, the evaluated results of our SQL query, will be be embedded into the subsequent error message. Prepending a full stop or a colon (we use the hex representation of 0x3a below) to the beginning of the XML query will ensure the parsing will always fail, thus generating an error with our extracted data. Note that this only works on MySQL version 5.1 or later. Use the LIMIT function to cycle through database information.

The functions used below combine to produce a query which is accepted by the MySQL compiler but errors at runtime. The error is then returned, but it evaluates and includes the subquery (due to the double select), thus returning the results of our injection to the page. Increment the first LIMIT to cycle through the database information.

Retrieve database version:

When no data or error messages are returned, you can use time delays or true/false responses to retrieve database information. Note that automated tools such as sqlmap significantly speed up the process.

This type of extraction is used when the application returns differing results dependent on whether the SQL query we inject evaluates to true or false. If we convert each individual character of the piece of database information we wish to retrieve to their decimal representation using the ASCII function (table here), we can create true or false conditions using the greater than, less than and equals symbols. We can then cycle through the individual characters using the SUBSTRING function and the pieces of database information using the LIMIT function.

In-band injection enables threat actors to utilize one communication channel to launch an attack and retrieve data. It requires using a vulnerability to force data extraction. Typically, the vulnerability allows code to output an SQL error from the server instead of the required data. This error enables the actor to understand the entire database structure.

If the attacker knows the vendor and version of the database engine, they can try more advanced techniques. Using vendor-specific queries, they can extract specific data that can help them plan additional attacks.

Using prepared statements with variable bindings is the most secure way to write database queries. It is also easier for programmers to work with than dynamic queries. In a parameterized query, the developer must first define all the SQL code and then pass each parameter to the query.

This coding style creates a separation between code and data, regardless of user input provided, which prevents almost all SQL injection attacks. Prepared statements prevent an attacker from changing the intent of a query, even if a malicious SQL command is injected into user inputs.

Stored procedures require developers to write SQL statements which are automatically parameterized. The difference between prepared statements and stored procedures is that the SQL code for the stored procedure is defined and stored in the database itself, then called by the application.

It is quite rare for applications to change database structure at runtime. In most cases, operations like creating or dropping tables, or modifying columns in a table, are performed during release windows and not during runtime.

Some parts of a SQL query do not permit the use of bind variables (such as table and column names) or sorting indicators (such as ASC or DESC). Use input validation or query redesign to prevent these types of illegal inputs. Whenever table or column names are used, these values should come from the code and not from user inputs.

Bright Security is a next-generation dynamic application security testing (DAST) solution which helps automate the detection and remediation of SQLi early in the development process, across web applications and APIs.

By shifting DAST scans left, and integrating them into the SDLC, developers and application security professionals can detect vulnerabilities early, and remediate them before they appear in production. Bright Security completes scans in minutes and achieves zero false positives, by automatically validating every vulnerability. This allows developers to adopt the solution and use it throughout the development lifecycle.

SQL Injection is a web application security vulnerability, which allows an attacker to "Inject" and execute his/her SQL queries in the context of the web application/website running. This is generally done by injecting SQL queries in GET or POST HTTP Requests which are then passed to the DBMS system without proper sanitisation.

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands - OWASP Project

So, from above it looks like "support" variable in URL containing value "yes" is used in the internal SQL Query to display the table since the value in "SUPPORT" column is always"YES". Now let's play with the URL for SQL Injection:

How can we find the number columns in the table in which the SQL commands are running. First approach is that you can guess number of columns from the data displayed in the browser or you can test by trial-and-error method using the ORDER BY keyword.

ORDER BY 20 fails here, because column number 20 does not exist therefore columns are less than 20. And %23 is the URL encoded form of # which will comment out further portion of the statement which can fail the injection.

The table displayed in the browser shows six columns, but in many instances the output columns and internal SQL columns will not be same because the webpage might not want to display the result from all the columns in DBMS table . But that case is not here, it looks like the page displays all the columns.

Now, we have successfully found the number of columns, which we will use for UNION based attack, now the problem again arises that UNION needs exact number of columns and same data-types (or convertible data-types ). How will we do it ?. We can just substitute NULLs in place of columns of the second table and try to gather data by injecting commands into columns of 6 columns available and injectable.

Now there is a problem the injected query also returns the data from the first table of UNION keyword, So how can we fix it. We can force the WHERE clause to fail by issuing an AND 1=0 or similar constraints which are always false so the first portion of UNION will fail and only second part will be shown.

Here we have enumerated the MySQL's information_schema.columns table and table_schema column to find the databases available to us, because information_schema.columns contains information regarding available databases to the user, table names, column names and etc . We have assumed that information_schema database to exist, because by default it is available in all versions of MySQL 5.

3a8082e126