SQL Injection and Database Security

Databases store and organise data that generally shares common characteristics. They are used for different purposes across desktop, web, mobile, and embedded-system platforms.

Databases may contain critical information relating to individuals and organisations. A company's operational data, for example, may be held in databases. Similarly, the production and control systems of a factory may operate on a database foundation.

Database systems therefore have a direct effect on the security of the software and wider system in which they operate. SQL, the query language used to perform database operations, has been used for many years and has consequently become a target for attackers.

This article evaluates SQL injection and the associated processes that can significantly affect the security of databases and connected systems.

DATABASE (DB)

Computer systems use different methods to store and organise data. A database is generally defined as an area in which related information is stored and structured through appropriate methods [2].

File systems such as NTFS, FAT, and ext4 determine how directories and files are stored on a disk. Operating systems form an intermediate layer between hardware and software in this process.

Data may be stored directly in files or in databases that offer more capable query facilities. Databases are designed as a higher-level layer that provides purpose-specific data management beyond a basic file system. Databases still store information in files, but they additionally provide more advanced organisation through mechanisms such as indexing.

Database architectures use different design models according to their purpose. Data is stored in rows and columns across different tables according to its attributes. Correct normalisation and indexing of these tables provide a more effective organisation of the recorded data.

DATABASE MANAGEMENT SYSTEM (DBMS)

A database management system is the software that manages recording and other operations for a database. Every database system contains components or servers that process submitted queries and commands.

Microsoft Access, MongoDB, MySQL, Oracle, SQLite, Sybase, Informix, PostgreSQL, and SQL Server are examples of database systems with different properties. Various data-query techniques have been developed for each database system and server.

STRUCTURED QUERY LANGUAGE (SQL)

SQL stands for Structured Query Language.

Query processes differ between databases and database management systems. SQL is one of the most widely used methods for querying data in many database systems [7].

Operations such as adding a new record and searching, listing, editing, deleting, copying, or moving existing records are commonly performed with SQL.

Commands shared by many database systems are summarised below.

ALTER DATABASE Modifies database properties.

ALTER TABLE Modifies table properties.

CREATE DATABASE Creates a database.

CREATE INDEX Creates an index.

CREATE TABLE Creates a table.

DELETE Deletes records.

DROP INDEX Deletes an index.

DROP TABLE Deletes a table.

INSERT INTO Adds a new record.

SELECT Selects and lists records.

TRUNCATE TABLE Empties a table.

TYPES OF SQL COMMAND

SQL commands are commonly grouped into four principal language categories: DQL, DDL, DML, and DCL.

DQL (Data Query Language)

DQL contains commands used to query data already present in a database. Its general purpose is to read selected data under specified constraints.

The SELECT command selects data from a table or command set. The table follows the FROM clause, conditions follow WHERE, a pattern follows LIKE, and columns used for ordering follow ORDER BY.

SELECT * FROM books

SELECT * FROM books WHERE name='Nutuk'

SELECT name, author FROM books WHERE author LIKE '%Kemal%'

SELECT name FROM books WHERE category='mathematics' ORDER BY sales DESC

DDL (Data Definition Language)

DDL defines fundamental components such as databases, tables, and indexes.

The CREATE command creates a database, table, or index.

CREATE DATABASE library

CREATE TABLE books (id INT PRIMARY KEY, name CHAR(50), author CHAR(50))

CREATE UNIQUE INDEX author_index ON books(author)

The ALTER command changes a table definition.

ALTER TABLE books ADD subject CHAR(50)

ALTER TABLE books DROP COLUMN subject

ALTER TABLE books MODIFY subject CHAR(60)

The DROP command removes the relevant database object or index.

DROP TABLE books

DROP INDEX book_index

The TRUNCATE command clears the records in a table together with values such as an automatically increasing identifier. It does not support limiting parameters such as WHERE. The source describes the operation as not recording individual row operations in the transaction log.

TRUNCATE TABLE books

DML (Data Manipulation Language)

DML concerns stored data and includes commands that change that data.

The DELETE command removes one or more records. The operation is recorded in the transaction log and can be limited with a condition such as WHERE. Care must be taken to target the intended data.

DELETE FROM books

DELETE FROM books WHERE id=2020

The UPDATE command changes one or more records. Like DELETE, it uses WHERE to identify the target records. Fields to be changed are specified with SET, and multiple assignments may be separated by commas.

UPDATE books SET author='Muhammet Ali Köker'

UPDATE members SET password='Aeda.2021' WHERE username='alikoker'

UPDATE members SET first_name='Muhammet Ali', last_name='Köker' WHERE username='alikoker'

The INSERT command adds data to a table.

INSERT INTO books (name, author) VALUES ('Nutuk', 'Mustafa Kemal Atatürk')

INSERT INTO people (first_name, last_name, profession) VALUES ('Muhammet Ali', 'Köker', 'Computer Engineer')

DCL (Data Control Language)

DCL includes commands that perform operations such as controlling authorisation over database objects.

The GRANT command assigns permissions for objects such as databases and tables to a specified user.

GRANT SELECT ON books TO .

GRANT SELECT ON books TO alikoker WITH GRANT OPTION

The REVOKE command withdraws permissions that were previously granted.

REVOKE SELECT ON books TO .

SQL INJECTION

SQL injection is a technique for causing malicious commands to be executed in systems that build and run SQL queries. The target may be a website, desktop application, or mobile application.

Software accesses a database at the application layer wherever users sign in, read system data, or edit related data [8].

A website, for example, serves client requests through a web server. That server also communicates with a database server and executes commands. SQL injection becomes possible when requests reaching this layer can be manipulated to alter the intended command.

A common demonstration shows how a user-login form may be bypassed without a valid password.

SQL Injection in Web Software

A web form sends client data to a server through controls defined by HTML elements.

In the example, the user enters data through a client-side form.

The server verifies the username and password with SQL. If the variables are concatenated directly into a command string without protection, an attacker can manipulate the resulting query for a malicious purpose. Additional database-security measures include restricting the permissions of the database account used by the connection and keeping credentials in protected configuration files.

The example form can be manipulated through SQL injection, resulting in access to the server-side system without the intended password check.

Information may also be transmitted in a URL. With a GET request, values are carried in the query string at the end of the URL, while other forms of direct transmission are also possible. If the SQL-injection weakness remains, passwordless access may be possible regardless of the transport used.

SQL INJECTION IN DESKTOP SOFTWARE

The same type of weakness may occur in desktop applications. The original example demonstrates SQL injection in a Windows Forms application developed with C# and .NET.

SQL INJECTION IN MOBILE SOFTWARE

A mobile application can also be affected if it constructs SQL queries from untrusted input. Some Android applications, for example, use a SELECT query to list usernames matching a keyword entered by the user. Without adequate controls, SQL injection may permit changes to the database or access to other information stored in it.

DATABASE MODIFICATION

An attacker may use SQL injection to add, change, or delete database data. In the original example, the demonstrated command deletes the records in a table named MyTable.

VULNERABILITY DISCOVERY

An attacker may use SQL injection to search for weaknesses in a target system. The original material illustrates this discovery process.

DETECTION AND ATTACK TOOLS

SQL injection can be attempted through a web browser or other software that accesses the network. Tools also exist to assist attackers and authorised penetration testers. SQLMap and jSQL Injection are examples.

SQL INJECTION BY DATA TYPE

SQL-injection operations are described here under two fundamental data types:

Numeric-data SQL attacks: These commonly target identifier values such as an id.

String-data SQL attacks: These target values represented as character strings.

SQL INJECTION BY DATA-RETRIEVAL METHOD

SQL-injection attacks are also described under two data-retrieval methods:

In-band SQL attacks: The attacker uses the same communication channel before and during the attack.

Out-of-band SQL attacks: The attacker uses a channel different from the primary communication channel, often through operations that write data to a file or transfer it by another mechanism.

ERROR-BASED SQL INJECTION

These attacks cause the database to produce error messages so that weaknesses and information can be inferred [5]. Two common forms are described:

Union-based SQL attacks: These use the UNION operator to combine result sets from SELECT queries and retrieve a larger body of data through a single HTTP response.

Double-query SQL attacks: These combine two different SQL queries into a single operation and are described as a comparatively fast attack method.

INFERENCE-BASED SQL INJECTION

In these attacks, the attacker first sends data to the server and then observes the response and behaviour. They are evaluated under two groups:

Boolean-based: The attacker infers a true or false result from the outcome of a SQL query sent to the database.

Time-based: The attacker determines whether a request reached the database and, if so, how much time elapsed. Commands such as sleep() and benchmark() are commonly used in this process.

PREVENTION METHODS

Many measures can be used to reduce the risk of SQL injection.

Database software and other software components should be kept up to date against known security weaknesses.

Network-traffic monitoring software and systems should be used.

Database firewalls designed specifically for database servers should be deployed and configured [9].

Database traffic should be monitored continuously for SQL injection, data breaches, and attacks involving privileged users.

Software capable of detecting relevant attacks and providing proactive protection should be used.

Activity-monitoring and audit or logging systems should record relevant database operations.

Signature-analysis methods specialised for database security may be used to help prevent intrusion.

White-list and black-list filtering methods may be applied to database-related operations to reduce the risk of data breaches.

Critical data should be masked and hidden from users who are not authorised to view it.

Stronger password policies should be enforced for database accounts and users recorded in the application database, and passwords should be changed periodically where the security policy requires it.

To reduce the consequences of a possible data breach, password-like values should be stored through one-way digest mechanisms rather than as plain text; the source gives SHA-256 as an example.

Input received from forms, URL parameters, and cookie values should be controlled so that SQL-related punctuation cannot alter the intended query.

Inputs such as e-mail addresses and usernames should first undergo validation.

Values expected to be numeric should be converted to integer values.

Server-side code should use parameter APIs supplied by the programming language and database library, such as methods on SqlParameterCollection, instead of concatenating input into SQL.

Database error messages that could disclose weaknesses should be hidden from end users.

Unnecessary code paths that access the database should be removed or disabled to reduce the attack surface.

Common security-hardening practices for access management and risk analysis should be applied when configuring the database system.

Network firewalls, web-application firewalls (WAF), and database firewalls (DBF) may be positioned between the user, application, and database as shown in the original material.

CONCLUSION

SQL injection is an important attack class that threatens databases and the systems connected to them. It may allow critical data to be obtained, operations to be manipulated, or the target system to be damaged.

The resulting losses may affect finances and reputation, and in critical contexts may create consequences extending to national security.

A system forms a whole with all of its components. Protecting the system therefore requires the security of each subsystem, including the database and every application layer that accesses it. Preventive processes should be treated accordingly.

REFERENCES

Anley, C. (2002). Advanced SQL Injection in SQL Server Applications.

Bandhakavi, S.; Bisht, P.; Madhusudan, P.; and Venkatakrishnan, V. N. (2007, October). “CANDID: Preventing SQL injection attacks using dynamic candidate evaluations.” Proceedings of the 14th ACM Conference on Computer and Communications Security, pp. 12–24.

Boyd, S. W., and Keromytis, A. D. (2004, June). “SQLrand: Preventing SQL injection attacks.” International Conference on Applied Cryptography and Network Security, pp. 292–302. Springer, Berlin and Heidelberg.

Clarke-Salt, J. (2009). SQL Injection Attacks and Defense. Elsevier.

Halfond, W. G.; Viegas, J.; and Orso, A. (2006, March). “A classification of SQL-injection attacks and countermeasures.” Proceedings of the IEEE International Symposium on Secure Software Engineering, vol. 1, pp. 13–15.

Kemalis, K., and Tzouramanis, T. (2008, March). “SQL-IDS: A specification-based approach for SQL-injection detection.” Proceedings of the 2008 ACM Symposium on Applied Computing, pp. 2153–2158.

Kieyzun, A.; Guo, P. J.; Jayaraman, K.; and Ernst, M. D. (2009, May). “Automatic creation of SQL injection and cross-site scripting attacks.” 2009 IEEE 31st International Conference on Software Engineering, pp. 199–209.

Junjin, M. (2009, April). “An approach for SQL injection vulnerability detection.” 2009 Sixth International Conference on Information Technology: New Generations, pp. 1411–1414.

Shar, L. K., and Tan, H. B. K. (2012). “Defeating SQL injection.” Computer, 46(3), 69–77.

Sadeghian, A.; Zamani, M.; and Abdullah, S. M. (2013, September). “A taxonomy of SQL injection attacks.” 2013 International Conference on Informatics and Creative Multimedia, pp. 269–273.

Spett, K. (2002). “SQL injection.” Things Security and Data Protection. Springer International Publishing.