Tony Marston's Blog About software development, PHP and OOP

How Radicore prevents SQL Injection attacks

Posted on 17th July 2021 by Tony Marston
Introduction
How to prevent SQL Injection
The steps taken in the RADICORE framework
Use of Prepared Statements
Use of Stored Procedures
Allow-list Input Validation
Escaping All User Supplied Input
Enforcing Least Privilege
Allow-list Input Validation as a Secondary Defense
References
Comments

Introduction

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.

How to prevent SQL Injection

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:

The steps taken in the RADICORE framework

These are the steps taken by the RADICORE framework to prevent SQL Injection:

Use of Prepared Statements

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.

Use of Stored Procedures

I do not use stored procedures anywhere. Never have, never will. Refer to Stored Procedures are EVIL for further details.

Allow-list Input Validation

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:

Escaping All User Supplied Input

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:

Enforcing Least Privilege

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.

Allow-list Input Validation as a Secondary Defense

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:


References


counter