SQL injection is when a hacker gains access to our database because we used their malicious user input to build a dynamic SQL query.
Let's say we have an input field that takes a phone number, and we use it to build this SQL query:
#define MAX_SQL 1024 // Support SQL statements up to 1024 characters long, including '\0'
char sqlText[MAX_SQL];
snprintf(sqlText, sizeof(sqlText), "SELECT * FROM customers WHERE phone = '%s';", phoneInput);
We’re expecting something like "8015550198" which would neatly build:
SELECT * FROM customers WHERE phone = '8015550198';
But what if a user enters "1' OR 1=1;--"?
Then we’d have:
SELECT * FROM customers WHERE phone = '1' OR 1=1;--';
Which will return the data for every customer because the WHERE clause will always evaluate to true! (1 always equals 1, and the "--" comments out the last single quotation mark.)
With the right input and queries, SQL injection can let hackers create, read, update and destroy data.
So to prevent SQL injection, we'll need to look at how we build and run our SQL queries. And we can think about some smart technical design in our application.
Here are five ways to protect ourselves:
1. Use stored procedures or prepared SQL statements. So do not build dynamic SQL. This is the most effective way to prevent SQL injection.
For example, we could build a prepared statement:
// initialize MySQL library
if (mysql_library_init(0, NULL, NULL)) {
// handle error
}
// create connection object
MYSQL *connection = mysql_init(NULL);
if (connection == NULL) {
// handle error
}
// connect to database server
if (mysql_real_connect(connection, "localhost", "root", NULL, "bakery", 0, NULL, 0) == NULL) {
// handle error
}
MYSQL_STMT *preparedStatement = mysql_stmt_init(connection);
if (preparedStatement == NULL) {
// handle error
}
const char *sql = "SELECT * FROM customers WHERE phone = ?";
if (mysql_stmt_prepare(preparedStatement, sql, strlen(sql)) != 0) {
// handle error
}
// fill input parameter data
MYSQL_BIND parameters[1];
unsigned long parameters_length[1];
my_bool parameters_is_null[1];
memset(parameters, 0, sizeof(parameters));
parameters_is_null[0] = 0;
parameters_length[0] = strlen(inputPhone);
parameters[0].buffer_type = FIELD_TYPE_STRING;
parameters[0].is_unsigned = 0;
parameters[0].is_null = ¶meters_is_null[0];
parameters[0].length = ¶meters_length[0];
parameters[0].buffer = (void *)inputPhone;
parameters[0].buffer_length = parameters_length[0];
// bind input parameter
if (mysql_stmt_bind_param(preparedStatement, parameters) != 0) {
// handle error
}
if (mysql_stmt_execute(preparedStatement) != 0) {
// handle error
}
Or we could build a stored procedure get_customer_from_phone with a string parameter inputPhone:
DELIMITER //
CREATE PROCEDURE get_customer_from_phone
(IN input_phone VARCHAR(15))
BEGIN
SELECT * FROM customers
WHERE phone = input_phone;
END //
DELIMITER ;
which we could call like this:
if (mysql_library_init(0, NULL, NULL)) {
// handle error
}
MYSQL *connection = mysql_init(NULL);
if (connection == NULL) {
// handle error
}
if (mysql_real_connect(connection, "localhost", "root", NULL, "bakery",
0, NULL, 0) == NULL) {
// handle error
}
MYSQL_STMT *preparedStatement = mysql_stmt_init(connection);
if (preparedStatement == NULL) {
// handle error
}
const char *sql = "CALL get_customer_from_phone(?)";
result = mysql_stmt_prepare(preparedStatement, sql, strlen(sql));
if (result != 0) {
// handle error
}
// fill input parameter data
MYSQL_BIND parameters[1];
unsigned long parameters_length[1];
my_bool parameters_is_null[1];
memset(parameters, 0, sizeof(parameters));
parameters_is_null[0] = 0;
parameters_length[0] = strlen(inputPhone);
parameters[0].buffer_type = FIELD_TYPE_STRING;
parameters[0].is_unsigned = 0;
parameters[0].is_null = ¶meters_is_null[0];
parameters[0].length = ¶meters_length[0];
parameters[0].buffer = (void *)inputPhone;
parameters[0].buffer_length = parameters_length[0];
// bind input parameter
if (mysql_stmt_bind_param(preparedStatement, parameters) != 0) {
// handle error
}
if (mysql_stmt_execute(preparedStatement) != 0) {
// handle error
}
2. Validate the type and pattern of input. If you know you're looking for specific data—like an ID, name, or email address—validate any user input based on type, length, or other attributes.
For example, here’s one way we could validate a phone number:
BOOL ICKIsValidPhone(NSString *phoneNumber) {
NSString* pattern = @"^\\(?([0-9]{3})\\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$";
NSPredicate* predicate = [NSPredicate predicateWithFormat:@"self matches %@", pattern];
return [predicate evaluateWithObject:phoneNumber];
}
3. Escape special characters like quotes. This approach is a quick and easy way to reduce the chances of SQL injection, but it's not fully effective.
For example, let's say we want to escape backslashes, single and double quotes, new lines (\n and \r), and null (\0):
NSString *ICKEscapeInput(NSString *input) {
NSMutableString *escapedInput = [NSMutableString new];
for (NSUInteger i = 0; i < input.length; i++) {
switch ([input characterAtIndex:i]) {
case '\0': {
[escapedInput appendString:@"\\0"];
break;
}
case '\n': {
[escapedInput appendString:@"\\n"];
break;
}
case '\r': {
[escapedInput appendString:@"\\r"];
break;
}
case '\\':
case '\'':
case '\"': {
[escapedInput appendString:@"\\"];
break;
}
default: {
[escapedInput appendString:[input substringWithRange:NSMakeRange(i, 1)]];
break;
}
}
}
return escapedInput;
}
(See table 10.1 in the MySQL String Literals docs for a full list of special character escape sequences.)
When we escape our input, now our query will be:
SELECT * FROM customers WHERE phone = '1\' OR 1=1;--';
which isn't a valid query.
4. Limit database privileges. Application accounts that connect to the database should have as few privileges as possible. It's unlikely, for example, that your application will ever have to delete a table. So don't allow it.
5. Don't display database error messages to users. Error messages contain information that could tell hackers a lot of information about your data. Best practice is to give generic database error messages to users, and log detailed errors where developers can access them. Even better, send an alert to the dev team when there’s an error.