Security Fix
Home Up Logic in DB XPath Sun MySQL Rock MySQL Security Fix OLTP



Fast, reliable data access for ODBC, JDBC, ADO.NET and XML
WSSC 2008: An event dedicated to SOA and Web Services Security
Got SOX compliance?
Movielink Logo 88x31
Business Intelligence with R&R ReportWorks
IBM eserver xSeries 306m 8849 - P4 3.4 GHz
Memory
PROLIANT BL20P G3 XEON 3.6G 2P
iTunes Logo 88x31-1

Database Developer

SQL Injection Becomes Database Community Issue

Trends
Logic in DB

MySQL 5

Drivers
ODBC
JDBC
OLE DB
.NET

Podcast
SQL:2003

MS SQL 2005



Webcast

SQL:2003
MS SQL 2005

OLTP
benchmarks
I
nnoDB
XA

Case Study
Nine

MySQL and PostgreSQL Developers Cooperate to Resolve Common Security Problem

by Ken North

One of the troubling trends related to data access and SQL-powered web applications is the frequent announcement of SQL injection vulnerabilities. Every week there are reports of software that can be exploited when executing SQL queries.

Recently discovered SQL injection vulnerabilities in MySQL and PostgreSQL were corrected due to an unusual collaboration. Developers of the open source MySQL and PostgreSQL database managers cooperated to resolve a problem that was common to both platforms. The collaboration was the result of an agreement formed last year.

Developers of open source SQL platforms formed an Open Source Database Consortium last year during the Open Source Database Conference in Germany. They agreed to share security information and the SQL injection vulnerability was fixed as a result of the agreement. After the vulnerability was detected in PostgreSQL, developers checked other open source SQL products. They determined Apache Derby, BerkeleyDB and SQLite were not vulnerable, but MySQL was. Because of the agreement to cooperate, developers quickly provided solutions for both platforms.

MySQL Security Patch

The fix for the MySQL SQL injection problem is available with version 5.1.11 and later releases. A workaround is available for those unable to upgrade. If you are using MySQL 5.0.1 or later versions, you can use NO_BACKSLASH_ESCAPES SQL mode for the current connection or all clients:

SET GLOBAL sql_mode='NO_BACKSLASH_ESCAPES';

To set the mode for the current connection, use:

SET sql_mode='NO_BACKSLASH_ESCAPES';

PostgreSQL Security Patch

The PostgreSQL vulnerability affects users exposed to input that has not been sanitized, particularly in web-facing applications using multi-byte encoding. The security fix is available starting with versions 8.1.4, 8.0.8, 7.4.13 and 7.3.15. Besides updating their software, PostgreSQL users should also remove non-standard escaping techniques from applications, or use '' to escape quotes.

Sponsor Links
Fast, reliable data access for ODBC, JDBC, ADO.NET and XML

There is a workaround for disabling escaping changes with PostgreSQL. In the PostgreSQL configuration file (postgresql.conf), set "backslash_quote = on".

Flawed Thinking and SQL Security Practices

To some extent the current wave of SQL injection problems is a byproduct of techies whose attitude about software is similar to their pop culture mindset -- if it's older than a year, it's irrelevant. That's a flawed attitude when it comes to software development and database administration practices.

"Web Databases: Fun with Guests or Risky Business?" (Web Techniques, March 1999) discussed security practices for databases accessible from the web. The article discusses data hiding with views and stored procedures. It also recommends designing web commerce applications so menu displays and ordering are handled by stored procedures instead of executing ad hoc SQL queries.

To defend from SQL injection attacks, another alternative to the ad hoc query is the prepared statement. Unlike ad hoc queries, a prepare-and-execute query is a two step process. The query uses parameters in the prepare step. At execution time, the application substitutes data values for the parameters.


About the Author

Ken North is an author and consultant who teaches Expert Series seminars. He wrote Database Magic with Ken North (Prentice Hall), Windows Multi-DBMS Programming (Wiley) and the Database Developer columns for Dr. Dobb's and Web Techniques.


Database Server Watch SQL Summit Home Page Articles

Visit GridSummit.com (Grid Computing Knowledge Portal)

2006, Ken North Computing LLC, All rights reserved.