SQL injections

Example Table

We will create a table named users to demonstrate SQL injection:

    CREATE TABLE users (
        user_id INT PRIMARY KEY,
        username VARCHAR(50),
        password VARCHAR(50)
    );
    

Creating the Table

To create the table, use the following SQL command:

    CREATE TABLE users (
        user_id INT PRIMARY KEY,
        username VARCHAR(50),
        password VARCHAR(50)
    );
    

Result:

Inserting Data

To insert data into the table, use the following SQL command:

    INSERT INTO users (user_id, username, password) 
    VALUES (1, 'admin', 'admin123'), 
           (2, 'user', 'user123');
    

Result:

SQL Injection Example

Consider the following vulnerable SQL query:

    SELECT * FROM users WHERE username = 'admin' AND password = 'admin123';
    

If an attacker inputs ' OR '1'='1 as the password, the query becomes:

    SELECT * FROM users WHERE username = 'admin' AND password = '' OR '1'='1';
    

Result:

Preventing SQL Injection

To prevent SQL injection, use prepared statements or parameterized queries:

    -- Example in PHP
    $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
    $stmt->execute(['username' => $username, 'password' => $password]);
    

Result:

    $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
    $stmt->execute(['username' => $username, 'password' => $password]);
                

Important Considerations

When securing your SQL queries, consider the following:

• Always use prepared statements or parameterized queries.
• Validate and sanitize user inputs.
• Use ORM frameworks that handle SQL injection prevention.
• Regularly update your database and application software to patch known vulnerabilities.