Privia Security was chosen as one of Türkiye's fastest growing companies!
Privia Security was chosen as one of Türkiye's fastest growing companies!
SQL Injection is a critical security vulnerability that allows attackers to send unauthorised SQL queries to a database. Attackers who are able to exploit this vulnerability can access critical data in the database and perform operations on it such as insertion, deletion and modification. The SQL Injection vulnerability ranks first on the OWASP 2017 TOP 10 list.
Below, an example is given in which SQL Injection is exploited to obtain the version information of a database management system. Figure 1.1 shows a sample laboratory environment in which the demonstration is performed.
In this scenario, an attacker with IP address 192.168.35.1 triggers an SQL Injection vulnerability via the id parameter of the “blog.aspx” page in the web application running on an IIS server at IP address 192.168.35.149, and sends SQL queries to the MSSQL (Microsoft SQL Server) at IP address 192.168.35.150. Figure 1.2 shows the blog.aspx page in the web application.
Records in the database are listed on the web page according to the id parameter on the page. However, this parameter contains an SQL Injection vulnerability. In Figure 1.3, the vulnerability was triggered by assigning a ” ‘ (single quote)” character to the id parameter.
When a request was made with a ‘ (single quote) appended to the id parameter, an error message was received from the SQL server. The reason for this is that the web application interprets the value assigned to the id parameter as a continuation of the SQL query. Therefore, assigning a ‘ (single quote) to the id parameter corrupted the query, causing an error message to be returned, and it was understood from this error that an SQL Injection vulnerability exists. Attackers can execute their own SQL queries by appending additional queries to the id parameter that continue the existing query. In Figure 1.4, the version information of the SQL server was obtained by assigning a SELECT statement (1 UNION SELECT 1,’SQL INJECTION’,@@version–) along with the UNION keyword to the id parameter.
In Figure 1.5, the SQLMAP tool was used to obtain the names of the databases on the SQL server.
Attackers can exploit this vulnerability to run queries designed to corrupt the SQL server. This article discusses preventing SQL Injection vulnerabilities using “SQL Server Stored Procedures”.
A Stored Procedure is a snippet of SQL code configured to execute certain SQL queries. When a stored procedure is first created, it compiles the SQL queries, and subsequent stored procedure calls do not compile these queries again. For this reason, having SQL queries within stored procedure objects saves time and increases performance.
When a stored procedure is properly constructed, SQL Injection attacks can be prevented. This is because, with a stored procedure, user inputs are accepted as a value rather than as a continuation of the SQL query. Since user inputs sent to the SQL server can be treated as values by the stored procedure, all inputs that could trigger an SQL Injection vulnerability will not affect the SQL query.
In Figure 2.1, a stored procedure was defined that lists the data in the table according to the value coming from the id parameter.
Within this procedure, a numeric variable named @id, and text variables named @sqlcmd and @params were defined. The @id variable holds the value assigned to the id parameter from the web application, @sqlcmd holds the SQL query, and @params holds the definition of the @id variable. These variables were then used together with a built-in stored procedure called sp_executesql. Since the parameters accepted by the sp_executesql procedure are treated as values, if the value assigned to the id parameter is not a numeric value, the SQL query will not execute.
It would have been possible to run the SELECT query directly within the procedure. In Figure 2.2, a stored procedure was defined that runs a SELECT query directly.
However, after assigning a numeric value to the id parameter in the web application, attackers can append their own queries and trigger the SQL Injection vulnerability. The reason for this is that although the query inside the stored procedure treats the value assigned to the id parameter as a value, it includes what is written after it in the query. For example, if 1;DROP TABLE dbo.music— is assigned to the id parameter, the value 1 before the ” ; (semicolon)” is a numeric expression, so the procedure will accept it. The SQL Server will then treat the semicolon as a character ending this query and execute the DROP TABLE dbo.music expression that follows it as an SQL query. The sp_executesql procedure prevents this situation and does not accept any input unless the value assigned to the id parameter is purely numeric. For example, if 1;DROP TABLE dbo.music— is again assigned to the id parameter, sp_executesql will handle this expression in its entirety and the SQL query will not execute because the expression does not consist solely of numeric characters. Figure 2.3 shows the C# code that calls the procedure in Figure 2.1 and assigns the id parameter to this procedure as a value.
The web application containing this code was run again and a query that could trigger the SQL Injection vulnerability was assigned to the id parameter. In Figure 2.4, a query that could trigger the SQL Injection vulnerability has been assigned to the id parameter.
When the query was assigned and the request was made, an error was received from the SQL server. However, this error is not caused by the SQL Injection vulnerability. The error was received from the server because the id parameter did not accept a numeric value, and as long as a purely numeric value is not entered, no input will affect the SQL query.
Figure 2.5 shows the results of the SQLMAP tool being run again.
Author: Ramin Karimkhani
You May Be Interested In These
