Stored procedures are also vulnerable for SQL injection attacks > P2

Stored procedures are also vulnerable for SQL injection attacks > P2

Stored procedures are also vulnerable for SQL injection attacks > P2

Previously we saw how stored procedures are also vulnerable for SQL injection attacks. Now lets see how to avoid such attacks.

An SQL-Injection Attack (SQLIA) is a subset of the unverified/unsanitized input vulnerability and occurs when an attacker attempts to change the logic, semantics or syntax of a legitimate SQL statement by inserting new SQL keywords or operators into the statement. This definition includes, but is not limited, to attacks based on tautologies, injected additional statements, exploiting untyped parameters, stored procedures, overly descriptive error messages, alternate encodings, length limits, second-order injections and injection of ”UNION SELECT”, ”ORDER BY” and ”HAVING” clauses. A detailed explanation of the different types and forms of SQLIAs and the ways in which they can be exploited are available in the public domain.

The widely deployed defense today is to train the programmers and web-developers about the security implications of their code and to teach them corrective measures and good programming practices.However, rewriting or revising the entire lot of existing legacy code is not an easy process and is not a financially viable option for many organizations. Even this does not guarantee any foolproof defense and hence we need automated processes to detect the vulnerability and eliminate them. Various other techniques like escaping the quotes and limiting the length of user inputs are employed as a quick fix solution. Unfortunately, even these security measures are only inadequate against highly sophisticated attacks. It is of even greater concern that well known database vendor products like Microsoft SQL Server etc. provide attackers direct access to the command line shell and registry using methods like xp cmdshell, xp regread etc. Some of the very recent incidents only highlight the magnitude of this problem and hence the urgent need to address it in an appropriate manner.

It is of even greater concern that well known database vendor products like Microsoft SQL Server etc. provide attackers direct access to the command line shell and registry using methods like xp cmdshell, xp regread etc. Some of the very recent incidents only highlight the magnitude of this problem and hence the urgent need to address it in an appropriate manner.

Various SQLIA detection techniques for the application layer have been proposed in literature, but none of them pay enough attention to SQLIA in stored procedures. Although the mechanism of SQLIA is the same for both stored procedure and application layer program, the same detection technique could not be applied to stored procedures, because of stored procedure’s limited programmability and the technique’s usability and deployability. Many existing techniques, such as filtering, information-flow analysis, penetration testing, and defensive coding, can detect and prevent a subset of the vulnerabilities that lead to SQLIAs. Techniques that employ input validation are prone to a large number of false positives and yet there is no guarantee that there are no false negatives. A simple example is to check for single quotes and dashes, and escape them manually. This could be easily beaten by using ASCII representation of these characters such as CHAR(0x27) for single quotes. Safe Query Objects and SQLDOM use encapsulation of database queries to provide a safe and reliable way to access databases but they require developers to learn and use a new programming paradigm. SQLrand provided a radical shift in the way this problem can be approached using query randomization. However, it could be circumvented if the key used for randomization were to be exposed. The use of a machine learning technique trained using a set of typical application queries to detect malicious query models at runtime was proposed by F. Valeur, D. Mutz and G. Vigna. However, like most other learning algorithms, it can generate a large number of false positives in the absence of an optimal query set for training. Another popular mechanism for application layer program has been static analysis of the code for vulnerabilities. The Java String Analysis library provides a mechanism for generating models for Java strings and can be extended to generate fairly accurate SQL-query models.Wassermann and Su combine static analysis with automated reasoning in to detect tautologies in the dynamically generated SQL queries, but the other forms of SQLIAs would still succeed rendering the system vulnerable.

We propose an SQL-Injection Attack prevention technique here that addresses all types of SQLIAs, as discussed in previous sections. The technique works by combining static analysis with runtime validation. The basis of such a technique is that the control flow graph of the stored procedures can be represented as an SQL-graph which indicates what user inputs the dynamically built SQL statements depend on. By using an SQL-graph, we reduce the set of SQL statements we need to verify, by looking at only a small subset of all the SQL statements in the stored procedure at runtime. During runtime, we retrieve a Finite State Automaton(FSA) from the EXEC(@SQL) procedure call and check the SQL statement with inclusion of user inputs for compliance, flagging them safe or unsafe.

To perform static analysis of the stored procedure, we propose a stored procedure parser which extracts the control flow graph from the stored procedure. We label all the EXEC(@SQL) statements in the control flow graph and then backtrack to identify all the statements involved in the construction of the @SQL statement in the control flow graph. In this process, an SQL-graph as explained below is generated. From the SQL-graph, SQL statements which depend on user inputs are selected and flagged to monitor their structure at runtime. At runtime, we compare the structure of the original intended SQL statement with the dynamically generated SQL statement having user inputs by using a Finite State Automaton. An SQLIA which alters the original structure will be flagged as unsafe and related information would be logged.

It is possible for a stored procedure to have more than one EXEC(@SQL) statement. Not all the EXEC(@SQL) statements would depend on the user inputs. Only those which need the user inputs to complete the SQL statements are potentially vulnerable to SQLIA. Given that the user input would realistically consist of a few strings only but the number of SQL statements that get executed in a stored procedure could be very large, we now try to optimize the number of queries that need to be further processed at runtime in order to ensure the validity and legitimacy of the dynamically generated statements, using an SQL-graph.

It represents 4 different SQL queries (EXEC statement hotspots) in the stored procedure as nodes within a logical boundary, and 3 different user inputs as being outside the logical boundary. If a particular user input (I) is used in a SQL query (Q), the relationship (R) between the two nodes is indicated by an undirected link between the 2 nodes. We now define dependencies (D) in the SQL-graph as links that point from one SQL query to another SQL query such that the user inputs used by the former is a proper superset of the user inputs used by the latter. For SQL queries that use the same set of user inputs, one of them is chosen as a representative query and is made to point to the others. We see the dependencies represented as directed arrows in the SQL-graph. Drawing equivalence to Code 1, Q1 represents the SQL statement, while I1 and

I2 represent the user inputs username and password. Q2, Q3, Q4 and I3 could possibly correspond to some other EXEC statements in the stored procedure not represented in the code snippet. The concept of an SQL-graph is used to reduce the runtime scanning overhead by restricting the number of queries that need to be scanned along any execution path that is taken in the stored procedure. SQL queries that do not use user inputs are not included in the SQL-graph. Only the SQL queries that are exposed to the user inputs in some form or the other (string manipulations included) are included in the SQL-graph representation. The choice of such a representation and the resulting benefits in terms of runtime overhead would be explained as part of Runtime Validation.

By using the stored procedure parser, we extract the control flow graph of the stored procedure and label every statement which contains an EXEC() call. Then we backtrack in the control flow graph using a breadth first search algorithm from the labeled statement, trying to find all the statements involved in the construction of the queries in the EXEC() call. During the process of searching, we keep track of how the query is incrementally built in the stored procedure. SQL queries are differentiated by labeling them according to their sequence in the control flow graph, and user inputs by their position in the arguments list of the stored procedure. After the search reaches the beginning of the stored procedure, an SQL-graph similar to above graph would be built.

[CODE]
CREATE PROCEDURE [EMP].[RetrieveProfile] @Name varchar(50),
@Passwd varchar(50)
IF LEN(@Name) > 0 AND LEN(@Passwd) > 0

SELECT @SQL=@SQL+‘NAME=“‘+MARK(@Name)+“‘
and ‘;
SELECT@SQL=@SQL+‘PASSWD=“‘+MARK(@Passwd)+““;

ELSE

SELECT @SQL=@SQL+‘NAME=“Guest“‘;

EXEC(SQLIACHECKER(@SQL))

[/CODE]

References :

websites : http://home.eng.iastate.edu/
1. G.T.B. et. al. Using parse tree validation to prevent sql injection attacks. SEM, 2005
2. W.G.J. Halfond and A. Orso. Amnesia: Analysis and monitoring for neutralizing sql injection attacks. ASE, 2005.

abhijeet on Facebookabhijeet on Linkedinabhijeet on Twitter
abhijeet
abhijeet
Abhijeet specializes in developing software. A full-stack developer and Entrepreneur, he takes an idea and crafts it into a beautiful product - front to back. He develops on the LAMP Stack (PHP, MVC, Web API, Perl, Python, Azure, AWS, Google Cloud) and utilizes AngularJS and Angular Material for a structured client. Abhijeet is a self-starter with experience working in remote, agile environments mainly focusing on the security constraints. This is the developer, Project Manager and Consultant you are looking for.