Database Developer SQL Injection Becomes Database Community Issue
Trends Logic in DB Moving Past MySQL 5 Java Drivers ODBC JDBC OLE DB .NET Podcast SQL:2003 MS SQL 2005 XML DBs XQuery Webcast XQuery SQL:2003 MS SQL 2005 OLTP benchmarks InnoDB XA Case Study Nine Sabre		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    © 2006, Ken North Computing LLC, All rights reserved.