Dark Reading is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Perimeter

10/2/2009
09:01 AM
Adrian Lane
Adrian Lane
Commentary
50%
50%

A Weapon Against SQL Injection

The single most common database security inquiry I get is, "What's this whole stored procedure parameter thing, and how does it help with SQL injection?"

The single most common database security inquiry I get is, "What's this whole stored procedure parameter thing, and how does it help with SQL injection?"With the Albert Gonzales hearings under way, SQL injection is in the headlines again. Security professionals I speak with are tired of having to react to SQL injection: Their current process is to nervously await a database vendor patch to fix the vulnerability before it can be exploited, and once they get it, hope that the patch doesn't break the application or database processes. CISOs want to know how they address SQL injection in advance, with minimal disruption to business processing functions.

It seems like every week I get questions from CISOs or application developers who are not well-versed about database internals, asking me how to secure databases. Conversely, I get questions from long-time database administrators who do not have a complete grasp of the security threats they need to be aware of. Most people do not know that databases can store and run programs internally. There are several variants, such as triggers and functions, but to combat SQL injection attacks, we use stored procedures.

Stored procedures are small programs inside the database, written in the databases native language: SQL. Oracle, Postrges, SQL Server, MySQL, and the rest have this capability. Conceptually, a stored procedure is nothing more than a bunch of queries lumped together and stored in the database. A stored procedure helps stop SQL injection because it processes input parameters differently than raw SQL statements.

A SQL statement may look something like the following: SELECT last_name, salary FROM emp WHERE emp_id =123;

This asks the database for the last name and salary of person who's employee number is 123. The same query could be embedded in a stored procedure saved within the database. Calling the stored procedure request would look something like this: get_salary(123);

What is important is not the query, but the stored procedure declaration, which might look something like this: PROCEDURE get_salary (IN emp_id NUMBER,OUT last_name CHAR,OUT salary REAL) BEGIN ... Think of any SQL statement as being similar to a Perl script, where it is interpreted and executed after it is received. A stored procedure is closer to a C/C++ program in that it must be compiled and linked in advance before it can be used. To "compile" the stored procedure, the database needs to resolve a lot of questions, such as the number, data type, and size of each parameter to be exchanged between the calling application and the database. The stored procedure must define the parameters exactly or it will fail to compile. This process even generates an execution plan, which is conceptually similar to a road map instructing the database on how to execute the stored SQL statements. When a SQL statement is passed to the database from an application, it is sent to the parser. It's the parser's job to figure out what task the user is trying to accomplish, and translate the human readable SQL into instructions for the database engine.

In its simplest terms, SQL injection is an attack where you confuse the statement parser into doing something it is not supposed to do via specially crafted statements or parameters. Contrast this with a stored procedure, where the database automatically verifies the parameters that are being passed in. Each input value is compared against the procedure's predefined requirements of data type and size.

If any of the parameters are missing or do not meet the definition specified within the procedure, then an error is generated and the statement(s) within the procedure are not executed. There is very little room for error or confusion because input parameters are validated before use. And unlike raw SQL statements, the database already knows how to process the request, and cannot be fooled into performing other actions. And aside from defending against a SQL injection attack, there are many positive benefits to stored procedures over ad-hoc SQL statements sent by an application. Stored procedures provide huge performance benefits, provide granular error-handling, and support optional subprograms that allow you to further inspect content.

Stored procedures can effectively harden an application, so consider transferring some of your riskier queries inside the database.

Adrian Lane is an analyst/CTO with Securosis LLC, an independent security consulting practice. Special to Dark Reading. Adrian Lane is a Security Strategist and brings over 25 years of industry experience to the Securosis team, much of it at the executive level. Adrian specializes in database security, data security, and secure software development. With experience at Ingres, Oracle, and ... View Full Bio

Comment  | 
Print  | 
More Insights
Comments
Newest First  |  Oldest First  |  Threaded View
NSA Appoints Rob Joyce as Cyber Director
Dark Reading Staff 1/15/2021
Vulnerability Management Has a Data Problem
Tal Morgenstern, Co-Founder & Chief Product Officer, Vulcan Cyber,  1/14/2021
Register for Dark Reading Newsletters
White Papers
Video
Cartoon
Current Issue
2020: The Year in Security
Download this Tech Digest for a look at the biggest security stories that - so far - have shaped a very strange and stressful year.
Flash Poll
Assessing Cybersecurity Risk in Today's Enterprises
Assessing Cybersecurity Risk in Today's Enterprises
COVID-19 has created a new IT paradigm in the enterprise -- and a new level of cybersecurity risk. This report offers a look at how enterprises are assessing and managing cyber-risk under the new normal.
Twitter Feed
Dark Reading - Bug Report
Bug Report
Enterprise Vulnerabilities
From DHS/US-CERT's National Vulnerability Database
CVE-2020-11997
PUBLISHED: 2021-01-19
Apache Guacamole 1.2.0 and earlier do not consistently restrict access to connection history based on user visibility. If multiple users share access to the same connection, those users may be able to see which other users have accessed that connection, as well as the IP addresses from which that co...
CVE-2020-27266
PUBLISHED: 2021-01-19
In SOOIL Developments Co., Ltd Diabecare RS, AnyDana-i and AnyDana-A, a client-side control vulnerability in the insulin pump and its AnyDana-i and AnyDana-A mobile applications allows physically proximate attackers to bypass user authentication checks via Bluetooth Low Energy.
CVE-2020-27268
PUBLISHED: 2021-01-19
In SOOIL Developments Co., Ltd Diabecare RS, AnyDana-i and AnyDana-A, a client-side control vulnerability in the insulin pump and its AnyDana-i and AnyDana-A mobile applications allows physically proximate attackers to bypass checks for default PINs via Bluetooth Low Energy.
CVE-2020-27269
PUBLISHED: 2021-01-19
In SOOIL Developments Co., Ltd Diabecare RS, AnyDana-i and AnyDana-A, the communication protocol of the insulin pump and its AnyDana-i and AnyDana-A mobile applications lacks replay protection measures, which allows unauthenticated, physically proximate attackers to replay communication sequences vi...
CVE-2020-28707
PUBLISHED: 2021-01-19
The Stockdio Historical Chart plugin before 2.8.1 for WordPress is affected by Cross Site Scripting (XSS) via stockdio_chart_historical-wp.js in wp-content/plugins/stockdio-historical-chart/assets/ because the origin of a postMessage() event is not validated. The stockdio_eventer function listens fo...