# SQLi

#### SQL Injection in Java

**Vulnerability:** SQL Injection

**Vulnerable Code:**

{% code overflow="wrap" %}

```java
javaCopy codeString query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);
```

{% endcode %}

**Reason for vulnerability:** This code directly concatenates user input into the SQL query, which allows an attacker to inject malicious SQL code.

**Fixed Code:**

{% code overflow="wrap" %}

```java
javaCopy codeString query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
```

{% endcode %}

**Reason for fix:** Using `PreparedStatement` with parameterized queries prevents SQL injection by treating user input as data, not code.

***

#### Vulnerable Code

```java
import java.sql.*;

public class UserDao {
    public User getUser(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/users", "root", "password");
        String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        // Process result set and return user
    }
}
```

#### Reason for Vulnerability:

This code constructs an SQL query by directly concatenating user input, allowing an attacker to manipulate the query structure.

#### Fixed Code:

```java
javaCopyimport java.sql.*;

public class UserDao {
    public User getUser(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/users", "root", "password");
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        ResultSet rs = pstmt.executeQuery();
        // Process result set and return user
    }
}
```

#### Reason for Fix:

The fixed code uses a PreparedStatement with parameterized queries, which separates SQL code from user input, preventing SQL injection attacks.

### Java Example

#### Vulnerable Code:

```java
javaCopy@Repository
public class ProductRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<Product> searchProducts(String category) {
        String jpql = "SELECT p FROM Product p WHERE p.category = '" + category + "'";
        return entityManager.createQuery(jpql, Product.class).getResultList();
    }
}
```

#### Reason for Vulnerability:

This JPA query is constructed by directly concatenating user input, allowing potential manipulation of the query structure.

#### Fixed Code:

```java
javaCopy@Repository
public class ProductRepository {
    @PersistenceContext
    private EntityManager entityManager;

    public List<Product> searchProducts(String category) {
        String jpql = "SELECT p FROM Product p WHERE p.category = :category";
        return entityManager.createQuery(jpql, Product.class)
                            .setParameter("category", category)
                            .getResultList();
    }
}
```

#### Reason for Fix:

The fixed code uses a parameterized JPQL query, which binds the user input as a parameter, preventing SQL injection attacks in JPA queries.

### Python Example

#### Vulnerable Code:

```python
pythonCopyimport sqlite3

def get_user(username):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")
    return cursor.fetchone()
```

#### Reason for Vulnerability:

This code uses string formatting to construct the SQL query, allowing an attacker to inject malicious SQL code.

#### Fixed Code:

```python
pythonCopyimport sqlite3

def get_user(username):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
    return cursor.fetchone()
```

#### Reason for Fix:

The fixed code uses parameterized queries, which treat user input as data rather than part of the SQL command, preventing SQL injection attacks.

***

**Vulnerable Code:**

```python
pythonCopy codequery = "SELECT * FROM users WHERE username = '%s'" % username
cursor.execute(query)
```

**Reason for vulnerability:** User input is directly used in the SQL query, allowing SQL injection.

**Fixed Code:**

```python
pythonCopy codequery = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))
```

**Reason for fix:** Use parameterized queries to prevent SQL injection.

***

## NoSQL Injection

**Example 1: JavaScript (Node.js with MongoDB)**

**Vulnerable Code:**

```javascript
javascriptCopy codeapp.post('/login', (req, res) => {
    const username = req.body.username;
    const password = req.body.password;
    db.collection('users').findOne({ username: username, password: password }, (err, user) => {
        if (user) {
            res.send('Login successful');
        } else {
            res.send('Login failed');
        }
    });
});
```

**Reason for vulnerability:** User input is directly used in the query, allowing NoSQL injection.

**Fixed Code:**

```javascript
javascriptCopy codeapp.post('/login', (req, res) => {
    const username = req.body.username;
    const password = req.body.password;
    db.collection('users').findOne({ username: username, password: hash(password) }, (err, user) => {
        if (user) {
            res.send('Login successful');
        } else {
            res.send('Login failed');
        }
    });
});
```

**Reason for fix:** Hash the password before querying the database to prevent injection.

**Example 2: Python (Flask with MongoDB)**

**Vulnerable Code:**

```python
pythonCopy code@app.route('/login', methods=['POST'])
def login():
    username = request.form['username']
    password = request.form['password']
    user = db.users.find_one({'username': username, 'password': password})
    if user:
        return 'Login successful'
    else:
        return 'Login failed'
```

**Reason for vulnerability:** User input is directly used in the query, allowing NoSQL injection.

**Fixed Code:**

```python
pythonCopy code@app.route('/login', methods=['POST'])
def login():
    username = request.form['username']
    password = hash_password(request.form['password'])
    user = db.users.find_one({'username': username, 'password': password})
    if user:
        return 'Login successful'
    else:
        return 'Login failed'
```

**Reason for fix:** Hash the password before querying the database to prevent injection.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://playbook.sidthoviti.com/devsecops/secure-coding/code-review-examples/sqli.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.