The next part (Part 7) of this DB2 Family Security Best Practices blog talks about preventing SQL injection in all your different applications. SQL injection is the ability of a hacker to provide extra SQL input into any of your interfaces or web sites and access data they want instead of what the application is supposed to access. By injecting extra SQL within your application, hackers are able to get access to private and other protected data.
Your application requires proper coding techniques to prevent this SQL injection vulnerability from being used. Application development has many different development languages, and all can suffer from poor coding techniques which can expose your application to the security risk of SQL injection. The following items discuss the ways to prevent SQL injection and other techniques for maximizing security within your application development coding efforts.
The first, and most important, way to guard against SQL injection in any programming language is to always use SQL Prepared Statements and parameter markers for any SQL variable within the SQL statement. In COBOL, Java, Hibernate, PHP, or any other application development language, applications need to use Prepared Statements and SQL parameter markers to prevent SQL Injection of malicious input from any type of application interface.
SQL Injection can be used for any type of SQL Select, Update, Insert, or Delete statement. It is vital that Prepared Statements and SQL parameter markers are used for all types of SQL statements within your application development code. Prepared Statements with parameter markers are very good for dynamic SQL and do not restrict SQL creativity in any way for any SQL type. Below is an example of a code snippet of a SQL Select COBOL Prepared Statement with a parameter marker that could retrieve rows from the Staff Table. This SQL model could be any type of SQL process that could be used to retrieve, update, insert, or delete data from the database safely without the exposure to SQL injection.
For example:
MOVE “SELECT NAME, DEPT, JOB FROM BEULKE.STAFF WHERE JOB = ?” TO TEMPSQL.
EXEC SQL
PREPARE SQLSTMT FROM :TEMPSQL
END-EXEC.
EXEC SQL
EXECUTE SQLSTMT USING :JOBPARM
END-EXEC.
Within your Java application services that support your web and batch processing, it is also critical that SQL injection is prevented by using Prepared Statements with SQL parameter markers. In Java services that are called by various different applications, it is critical to follow the standards of using Prepared Statements with SQL parameter markers because poor coding could expose a wider variety of applications data to hackers.
The following code snippet shows the “JOB = ?” SQL parameter marker code that provides the application program a parameter where a value can be used by the SQL query. The parameter marker value is set in the setString statement from the first argument (args[0] ) passed into the application Java service.
String query1 = “SELECT NAME, DEPT, JOB FROM BEULKE.STAFF WHERE JOB = ?”;
Stmt1 = conn.prepareStatement(query1);
Stmt1.setString(1,args[0]);
Resultset1 = stmt1.executeQuery();
Application development continues to use many package frameworks, open source code, and customized code that leverage SQL Prepared Statements and parameter markers correctly. Unfortunately, sometime the frameworks or customized code modules are modified and need to be verified.
Verifying the Prepare Statements and parameter markers is very easy. Through the appropriate interface for COBOL, count the number of question marks (“?”) in the code, and then count the number of parameters within the “USING” statements of the SQL.
Within Java application code GREP and/or count the question marks (“?”) and then count the number of “.set” statements within the Java application code. In both the COBOL and Java examination counts within the code, the number of Prepared Statements and parameter marker instances question marks and their related “USING” parameters and “.set” statements should match. Any count discrepancies warrant further investigation to make sure your application development code is safe against SQL injection and hacking through your interfaces.
Another area not to forget to check for SQL Infection exposure is within your stored procedures and/or SQL/PL interfaces. Remember any SQL interface can be used for SQL injection, so make sure to inspect all interfaces within your applications environments.
I authored two CIO DB2 Security Audit white papers and a technical DB2 Security Audit white paper for SEGUS. Also at the link is a webinar talking about the SEGUS security and DB2 audit considerations. The How to establish a Security Audit for DB2 z/OS white paper can be found here (https://www.segus.com/media/wp-security-audit-dba-en.pdf)” and the Is 339° Security Audit Sufficient? White paper is here (https://www.segus.com/media/wp-PSI-339-security-cto-en.pdf) and the Powerpoint webinar discussion presentation with SEGUS is here (https://www.segus.com/events/webinars/db2-for-zos-security-audit-protecting-your-assets/).
Here’s a list with links to other posts that may help you improve your DB2 performance, security, and ease-of-use:
5 More DB2 SQL Performance Tips
Another 5 More DB2 SQL Performance Tips
5 Big Data SQL Performance Tips – Fixing Generated SQL
DB2 11 SQL Performance Improvements
5 More SQL Performance Tips for your Big Data
Hadoop SQL: 4 Reasons Why BigInsights Is the Best
Vital Java DB2 SQL Performance Considerations
More DB2 Family Security Best Practices Part 7: Preventing SQL Injection
DB2 SQL Security Audits
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs. Follow him on Twitter here (@DBeulke) or connect through LinkedIn here (https://www.linkedin.com/in/davebeulke).
Leave a Reply