Skip to content

SQL Injection

What does this mean ?

SQL injection is when malicious code is injected into SQL statements via web page input. It is one of the most often used web hacking methods. When you ask a user for information, such as their username/userid, and instead of a name/id, the user provides you a SQL statement that you inadvertently run on your database, SQL injection happens.

What can happen ?

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), perform database administration operations (such as shutting down the DBMS), recover the content of a given file on the DBMS file system, and, in some cases, issue commands to the operating system. SQL injection attacks are injection attacks in which SQL commands are inserted into data-plane input to cause predetermined SQL instructions to be executed.

Recommendation

  • One classic technique to combating SQL injection attacks is to treat them like an input validation problem, accepting only characters from an allow list of safe values or identifying and escaping potentially dangerous data from a deny list.
  • An allow list may be a powerful tool for implementing tight input validation requirements, but parameterized SQL statements are easier to maintain and provide additional security assurances.

Sample Code

Vulnerable : 

var cmd = "SELECT * FROM Users WHERE username = '" + input + "' and role='user'";
ctx.Database.ExecuteSqlCommand(cmd);

Non Vulnerable : 

var cmd = "SELECT * FROM Users WHERE username = @username and role='user'";
ctx.Database.ExecuteSqlCommand(cmd, new SqlParameter("@username", input));

Vulnerable : 

String query = "SELECT * FROM Users WHERE Username=" +
          request.getParameter("username") +
          " AND Password=" +
          request.getParameter("password");

Non Vulnerable : 

String username = request.getParameter("username");
// Perform input validation on username detect attack strings

String sqlQuery = "SELECT password FROM user WHERE user_name = ? ";
PreparedStatement prepedStmt = connection.prepareStatement( sqlQuery );
prepedStmt.setString( 1, username);
ResultSet results = prepedStmt.executeQuery( );

Vulnerable : 

$name = $_POST['name'];
$message = $_POST['message'];

// check if this user already has a message
mysqli_query($conn, "SELECT * from messages where name = $name");

// Other code here

Non Vulnerable : 

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();

Vulnerable : 

app.post("/user", (req, res) => {
    const data = req.body;
    const query = `SELECT * FROM user WHERE id = (${data.id})`;
    conn.query(query, (err, rows) => {
        if(err) throw err;
        res.json({data:rows});
    });
});

Non Vulnerable : 

app.post("/user", (req, res) => {
    const data = req.body;
    connection.query('SELECT * FROM user where id = ?', [data.id], (err, rows) => {
        if(err) throw err;
        res.json({data:rows});
    });
});

Vulnerable : 

const mysql = require('mysql');
const conn = mysql.createConnection({ host: host, user: user, password: pass, database: db });
conn.connect(err => {
    conn.query('SELECT * FROM users WHERE id = ' + userinput, (err, res) => {}); // Vulnerable
});

Non Vulnerable : 

const mysql = require('mysql');
const conn = mysql.createConnection({ host: host, user: user, password: pass, database: db });
conn.connect(err => {
    conn.query('SELECT name FROM users WHERE id = ?', [userinput], (err, res) => {}); // Non Vulnerable
});

Vulnerable : 

id := "'10'";
query := fmt.Sprintf("SELECT name, email FROM users WHERE ID = %s, id);

Non Vulnerable : 

import (
    "database/sql"
    _ "github.com/lib/pq"
)

func main() {
    connStr := "user=pgtest dbname=pgtest sslmode=verify-full"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }

    age := 24
    rows, err := db.Query("SELECT name FROM users WHERE age = $1", age)
}

Vulnerable : 

if User.where("email = '#{address}' and password = '#{password}'").exists?
    # code
end

Non Vulnerable : 

if User.where("email = ? AND password = ?", address, password).exists?
    # code
end

References