> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Sql injection

# SQL Injection: A Comprehensive Guide

**SQL Injection** is a security vulnerability that occurs when an attacker can manipulate an SQL query by injecting malicious SQL code. This can lead to unauthorized access to data, data corruption, or even complete control over the database. Understanding and preventing SQL injection is crucial for building secure applications.

***

## Key Concepts of SQL Injection

1. **How SQL Injection Works**:
   * Attackers exploit vulnerabilities in input validation to inject malicious SQL code.
   * This can happen when user input is directly concatenated into SQL queries.

2. **Common Attack Scenarios**:
   * **Bypassing Authentication**: Injecting SQL to bypass login screens.
   * **Data Extraction**: Injecting SQL to retrieve sensitive data.
   * **Data Manipulation**: Injecting SQL to modify or delete data.
   * **Database Takeover**: Injecting SQL to execute system commands.

3. **Prevention Techniques**:
   * Use **parameterized queries** or **prepared statements**.
   * Validate and sanitize user input.
   * Use **stored procedures** with proper input validation.
   * Apply the principle of **least privilege** to database accounts.

***

## Example of SQL Injection

### Vulnerable Query

Consider a login form where the username and password are directly concatenated into an SQL query:

```sql theme={"system"}
SELECT * FROM Users WHERE Username = 'user' AND Password = 'pass';
```

If an attacker enters `' OR '1'='1` as the username and password, the query becomes:

```sql theme={"system"}
SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = '' OR '1'='1';
```

This query will return all rows in the `Users` table, effectively bypassing authentication.

***

## Example 1: Bypassing Authentication

### Vulnerable Code

```sql theme={"system"}
DECLARE @Username NVARCHAR(50) = 'admin';
DECLARE @Password NVARCHAR(50) = 'password';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Users WHERE Username = ''' + @Username + ''' AND Password = ''' + @Password + '''';
EXEC sp_executesql @SQL;
```

### Attack

If the attacker provides the following input:

* Username: `' OR '1'='1`
* Password: `' OR '1'='1`

The query becomes:

```sql theme={"system"}
SELECT * FROM Users WHERE Username = '' OR '1'='1' AND Password = '' OR '1'='1';
```

This query will return all rows, allowing the attacker to bypass authentication.

***

## Example 2: Data Extraction

### Vulnerable Code

```sql theme={"system"}
DECLARE @UserId NVARCHAR(50) = '1';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Users WHERE UserId = ' + @UserId;
EXEC sp_executesql @SQL;
```

### Attack

If the attacker provides the following input:

* UserId: `1; DROP TABLE Users; --`

The query becomes:

```sql theme={"system"}
SELECT * FROM Users WHERE UserId = 1; DROP TABLE Users; --';
```

This query will drop the `Users` table, causing data loss.

***

## Preventing SQL Injection

### 1. **Parameterized Queries**

Use parameterized queries to separate SQL code from user input.

```sql theme={"system"}
DECLARE @Username NVARCHAR(50) = 'admin';
DECLARE @Password NVARCHAR(50) = 'password';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT * FROM Users WHERE Username = @Username AND Password = @Password';
EXEC sp_executesql @SQL, N'@Username NVARCHAR(50), @Password NVARCHAR(50)', @Username, @Password;
```

**Explanation**:

* The user input is passed as parameters, preventing SQL injection.

***

### 2. **Stored Procedures**

Use stored procedures with parameterized inputs.

```sql theme={"system"}
CREATE PROCEDURE AuthenticateUser
    @Username NVARCHAR(50),
    @Password NVARCHAR(50)
AS
BEGIN
    SELECT * FROM Users WHERE Username = @Username AND Password = @Password;
END;

-- Execute the stored procedure
EXEC AuthenticateUser @Username = 'admin', @Password = 'password';
```

**Explanation**:

* The stored procedure ensures that user input is treated as parameters, not executable code.

***

### 3. **Input Validation and Sanitization**

Validate and sanitize user input to ensure it conforms to expected formats.

```sql theme={"system"}
DECLARE @UserId INT;

-- Validate that @UserId is an integer
IF ISNUMERIC(@UserId) = 1
BEGIN
    SET @UserId = CAST(@UserId AS INT);
    DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Users WHERE UserId = @UserId';
    EXEC sp_executesql @SQL, N'@UserId INT', @UserId;
END
ELSE
BEGIN
    PRINT 'Invalid UserId';
END
```

**Explanation**:

* The input is validated to ensure it is a numeric value before being used in the query.

***

### 4. **Least Privilege Principle**

Grant database accounts the minimum permissions necessary to perform their tasks.

```sql theme={"system"}
-- Create a restricted user with limited permissions
CREATE USER RestrictedUser WITHOUT LOGIN;
GRANT SELECT ON Users TO RestrictedUser;
```

**Explanation**:

* The `RestrictedUser` can only perform `SELECT` operations on the `Users` table, reducing the impact of a potential SQL injection attack.

***

### 5. **Use ORM Frameworks**

Use Object-Relational Mapping (ORM) frameworks like Entity Framework, which automatically parameterize queries.

```csharp theme={"system"}
var user = context.Users
    .Where(u => u.Username == username && u.Password == password)
    .FirstOrDefault();
```

**Explanation**:

* ORM frameworks generate parameterized queries, reducing the risk of SQL injection.

***

## Key Takeaways

1. **SQL Injection** occurs when attackers inject malicious SQL code into queries.
2. **Prevent SQL Injection** by:
   * Using **parameterized queries** or **prepared statements**.
   * Validating and sanitizing user input.
   * Using **stored procedures** with proper input validation.
   * Applying the **least privilege principle** to database accounts.
3. **ORM frameworks** can help automate the prevention of SQL injection.