SQL Injection is a type of attack used to compromise web applications. It is just one of several attacks which have been identified by the Open Web Application Security Project (OWASP). Its full description is as follows:
A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.
In SQL Injection Prevention Cheat Sheet the OWASP project states the following:
SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input. To avoid SQL injection flaws is simple. Developers need to either:
a) stop writing dynamic queries; and/or
b) prevent user supplied input which contains malicious SQL from affecting the logic of the executed query.
It then identifies the following options as Primary Defenses:
It also identifies the following options as Additional Defenses:
These are the steps taken by the RADICORE framework to prevent SQL Injection:
I do not use prepared statements anywhere. Never have, never will. I wrote all my database logic before prepared statements became available, and having written code that works I saw no reason why I should spend time in rewriting it to do the same thing differently.
I do not use stored procedures anywhere. Never have, never will. Refer to Stored Procedures are EVIL for further details.
The SQL Injection Prevention Cheat Sheet has this to say on this topic:
Various parts of SQL queries aren't legal locations for the use of bind variables, such as the names of tables or columns, and the sort order indicator (ASC or DESC). In such situations, input validation or query redesign is the most appropriate defense. For the names of tables or columns, ideally those values come from the code, and not from user parameters.
If any SQL query refers to a database, table or column name then these names are provided as literals within the code and NEVER taken, either directly or indirectly, from user input. The only parts of an SQL query which can be altered by user input are listed below. Note that these only apply in screens containing an application zone which lists multiple rows, such as a LIST screen.
By selecting one of these hyperlinks the web browser will send a GET request to the server, and this request will appear in the browser's address bar as the URL of the new page. Although a naughty user may be tempted to alter this URL directly before resending it to the server you should be aware that the following rules apply:
All user-supplied input comes from controls on an HTML document which are transmitted to the server as a POST request. Some of these controls may be text boxes while others can be dropdown lists, radio buttons or check boxes. POST requests are only used by screens which perform one or more of the following actions:
The entire POST array is passed in a single argument, and this will be processed by the _validateInsertPrimary() method to ensure that the data for each column conforms to its specifications in the $fieldspec array. Any validation errors will cause the operation to be cancelled, and appropriate error messages will appear in the screen. When the INSERT query is being constructed in the relevant DML object all string fields will have any non-blank values escaped to deal with any errant quote characters.
The entire POST array is passed in a single argument, and this will be processed by the _validateUpdatePrimary() method to ensure that the data for each column conforms to its specifications in the $fieldspec array. Any validation errors will cause the operation to be cancelled, and appropriate error messages will appear in the screen. When the UPDATE query is being constructed in the relevant DML object all string fields will have any non-blank values escaped to deal with any errant quote characters.
This performs data validation checks similar to those performed by the _validateInsertPrimary() method, but with the following exceptions:
When a user selects a task, either via a menu button or a navigation button, he is directing the framework to activate one of the many component scripts which exist is the server's file system. Each one of these scripts is designed to perform one or more operations on one or more table classes, and neither the operations nor the table classes can be altered by any user input.
While there may be thousands of different components (tasks or use cases) within the application, each user can only access those tasks to which permission has been granted via the Role Based Access Control components which have been built into the framework. Tasks which are not permitted for the user are hidden from any displays, and if they cannot be seen they cannot be selected.
I do not use any features in the database, such as views, stored procedures and user privileges, to restrict what a user can do within a particular task. If a user has access to a task then that task will do what it is allowed to do regardless of who is running it.
The SQL language has two distinct dialects - Data Definition Language (DDL) and Data Manipulation Language (DML). None of the tasks provided in the framework perform any DDL operations as these should only be performed using a separate database administration tool. Tasks are ONLY allowed to use DML operations to manipulate data within the current database structure and NEVER to use DDL operations to alter the database structure.
The application always uses the same user account to connect to the database, not a separate account for each user, as this is the only way to deal with connection pooling. This application account can therefore be granted only those database privileges which are required by the application components, such as including DML statements and excluding DDL statements.
In addition to being a primary defense when nothing else is possible (e.g., when a bind variable isn't legal), input validation can also be a secondary defense used to detect unauthorized input before it is passed to the SQL query. On the Input Validation Cheat Sheet page it states that this form of validation should be performed at the following levels:
This form of validation is known as primary validation and performed automatically by the framework by calling the standard validation object on the data array before the data is passed to the database. The relevant object method is called during the workflow of the insertRecord() or updateRecord() methods.
This form of validation is known as secondary validation and is defined within various "hook" methods within each table class. These hook methods, if they are defined within the table class, will be processed automatically by the framework before any data is passed to the database.
Columns which should only contain a value from a predetermined list will be converted from a text box to a dropdown list or a radio group. This will ensure that the user can only select one of the predetermined values and not one of his own.