Skip to content

SQL Injection

What does this mean ?

SQL injection is the placement of malicious code in SQL statements, via web page input. It is one of the most common web hacking techniques. SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.

What can happen ?

A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

Recommendation

  • One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a deny list of potentially malicious values.
  • An allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security.

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();

References