Fixing SQL Injection

SQL Injection is a vulnerability that occurs where user supplied input is insecurely concatenated into an SQL query. We showed how easy can be to detect in our Finding SQL Injection article, and we’ve run through exploitation in many posts such as our post on Exploiting Error-based SQL Injection.

However, in this post, we’re looking at fixing it. The fix is quite a simple code change. As the issue described is user input insecurely concatenated into a query, the remediation for SQL injection is fairly simple: don’t build queries through string concatenation. Instead, it’s more secure to use “Prepared Statements”, often called “Parameterized Queries”. All modern languages support this type of query either directly or through a framework; here we will supply a PHP example to show the difference.

An insecure example would be:

<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
/* Insecure (Non-prepared) example */
$query = "SELECT id FROM tblMembers WHERE username = " . $_GET["id"];
if (!($res = $mysqli->query($query))) {
    echo "ID query failed: " . $mysqli->error;
}
var_dump($res->fetch_all());
$mysqli->close();
?>

In the above example, the query is generated through string concatenation with user input, therefore it would be possible to alter the logic of the query to perform malicious actions by appending additional logic to the user input.

Instead, we should separate user input from the query by using a parameterized query, like this:
<?php
$mysqli = new mysqli("localhost", "user", "password", "world");
$stmt = $mysqli->stmt_init();
$stmt->prepare("SELECT id FROM tblMembers WHERE username = ?");
$stmt->bind_param("s", $_GET["user"]);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM)) {
    foreach ($row as $r) {
        echo "$r\n";
    }
}
$stmt->close();
$mysqli->close();
?>

Let’s look at another example, this time using Python, Flask and PyMySQL:
input_content = request.args.get("input")
database = mysql.connect(host="127.0.0.1",
    user="sqli1",
    passwd="aaaaaaaa",
    database="sqli1",
    use_unicode=True)

query = "SELECT name, count FROM animals WHERE name = '" + input_content + "'"
cursor = database.cursor()
cursor.execute(query)
output = cursor.fetchall()

Instead parameters should be used, like this:

input_content = request.args.get("input")
database = mysql.connect(host="127.0.0.1",
    user="sqli1",
    passwd="aaaaaaaa",
    database="sqli1",
    use_unicode=True)

query = "SELECT name, count FROM animals WHERE name = %s"
cursor = database.cursor()
cursor.execute(query, (input_content, ))
output = cursor.fetchall()

That’s it!

Read More

• Finding SQL Injection
• Error-based
• Union-Based
• Blind-Boolean
• Blind Time-based