This document provides information about SQL Injection attacks and how the risks associated with them are mitigated in the DataFlex application environment. When SQL statements written by a developer are included in an application program for direct execution by an SQL server, the developer is responsible for making sure that his or her programming style does not let the guard down for threats like SQL Injection or other risks.
SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker. Source: Microsoft
DataFlex applications using SQL are probably using one of Data Access DataFlex Connectivity Kits which can use SQL in two ways:
DataFlex database requests (find, save, etc.) are controlled by the compiled DataFlex program and must be converted to SQL requests. For each database request, the DataFlex Connectivity Kit creates SQL statements with parameters, also known as prepared statements. That means that those DataFlex generated SQL statements are compiled on the server, then user input is assigned to the prepared statement as parameters making it much, much harderalmost impossible to implement SQL injection attacks from input to a standard DataFlex program.
The reason that prepared statements help so much in preventing SQL injection is because the values that will be inserted into an SQL query are sent to the SQL server after the actual query is sent to the server. In other words, the data input is sent separately from the prepared query statement. This means that there is absolutely no way that the data input can be interpreted as SQL, and theres no way that a hacker could run his own SQL on your application. Any input that comes in is only interpreted as data, and cannot be interpreted as part of your own applications SQL code. Source: ProgrammerInterview.com
For example, if the structure of an update instruction for a row in a table is converted to:
UPDATE table SET field1 = ?, field2 = ?, WHERE RECNUM = ?
by the DataFlex Connectivity Kit, the statement will then be compiled via an SQLPrepare operation, then, if successful, the user input data is added to the compiled statement with SQLBind operations and finally an SQLExecute is performed to complete the update and modify data in the table.
So for table operations, since the combination of a DataFlex program and Connectivity Kit uses parametrized queries, i.e. prepared execution, applications are much safer from SQL injection.
Direct statements are SQL statements embedded by a developer in an applications program code. In such a case, the SQL statements are executed as written by the developer assuming they are valid.
Since direct SQL statements are being written by the developer and not an end user, direct statements can be safe if written competently and with knowledge of SQL Injection attack avoidance. However, if SQL code is written in a way that makes it vulnerable to SQL injection, the application will be then vulnerable, too. In other words, when using embedded SQL, an application is as safe as the direct statements that the developer writes. The developer must take responsibility for his own code; DataFlex cannot protect against developers risky SQL code.
The only place that DataFlex itself uses SQL directly is through the Data Dictionary (DD) SQL filters and those filters are set by the developer. To protect against SQL Injection attacks, DataFlex DD SQL filters have their strings double checked by escaping them (see below) on the small chance that the filter strings are obtained from an end-user via some kind of user input. In DataFlex for Linux there are no DD SQL filters so the above does not apply.
When using embedded SQL and DD SQL filters, developers should avoid allowing user input to be used directly in SQL statements. If, for example, a program has a search input where a user can compose any text (including SQL code!) as a search string, that text should not be used directly to compose an SQL statement. The developer should make sure that any such user input is first escaped and validated, and then used.
To assist developers in this task, DataFlex includes a function called SQLEscapedStr that changes a string to an "escaped" string that is better suited for being used in direct SQL statements. This function replaces a single quote with two single quotes, which helps protect against SQL injection.
In the programming world, escaping means:
allowing special characters (like single/double quotes, percent signs, backslashes, etc.) in strings to be saved so that they remain as part of the string, and are not misinterpreted as something else. For example, if we want to include a single quote in a string (like in the string its) that gets output to the browser [ ], then we have to add a backslash to the single quote so that it is still interpreted as a single quote when generating the output. Source: ProgrammerInterview.com
Also, developers writing embedded SQL should always validate user input by testing data type, length, format, and range. When implementing precautions against malicious input, developers should consider the architecture and deployment scenarios of the application. Remember that programs designed to run in a secure environment can be copied to a non-secure environment.
The following suggestions should be considered best practices for embedded SQL:
For example, data validation in a client-side application can prevent simple script injection. However, if the next tier assumes that its input has already been validated, any malicious user who can bypass a client can have unrestricted access to a system.
Char. Meaning in Transact-SQL
; Query delimiter.
Character data string delimiter.
-- Comment delimiter.
/* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.
xp_ Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell.
Source: Microsoft Technet
For further reading on best practices in validating user input, access SQL Injection.
John Tuohy, CTO, Data Access Worldwide
Martin Moleman, Software Engineer, Data Access Europe
DataFlex and SQL Injection
How to prevent SQL injection attacks?