Securing your MySQL queries from SQL injection in PHP

Hello!

There is many strategies for securing your code against malicious user input. Some frameworks have checks and balances built in. There are simple standard PHP functions that are designed to strip tags and illegal characters from variables like strip_tags and filter_var.

Filtering XSS in PHP

Those standard functions are good for basic input filtering, but will not protect you 100%. For example, with strip_tags, you can still escape the filter and inject javascript into the request :

http://domain.com/query?name="style="margin-top:10px;margin-left:50px;display:block;"onmouseover=alert(/XSS/[-1]);eval(name) a="

So what do you do? Well this post was written to mainly cover SQL injection with MySQL queries in PHP. Before we get to that, I’ll touch on one option which is to create your own customized input sanitize function. The function can be adjusted based on the queries, commands and risk factors that are dependent on the actual function of your code. Using str_replace, you could craft your own function to deal with input and customize it to suit your needs :

// Cross Site Script  & Code Injection Sanitization
function xss_cleaner($input_str) {
    $return_str = str_replace( array('<',';','|','&','>',"'",'"',')','('), array('<',':','|','&','>',''','"',')','('), $input_str );
    $return_str = str_ireplace( '%3Cscript', '', $return_str );
    return $return_str;
}

The above function basically searches and replaces any attempt to escape a variable or input in order to inject your own code into the query maliciously. The above function is intended only to illustrate one of the many different methods for sanitizing input.

Ensuring your MySQL queries are secure in PHP

Now to what we want to talk about! 🙂

In PHP when you are querying the database, you are typically taking user input from whatever source and crafting an SQL query in order to produce the results. The query can be a SELECT, INSERT or UPDATE or anything really. The key is that the input can potentially be escaped in order to inject a new SQL query in order to pull data, write data or maliciously interact with the database. If you are running an E-Commerce site that stores credit card or customer data (even if the cards or user data is encrypted with a salted hash), the risks and liability becomes very clear.

There are two strategies, in my mind, with respect to mitigating the risk and dealing with the problem. The first and most obvious is to write code with security always at the forefront. Security is a priority over everything else. Sometimes its a challenge to communicate this to project managers or other levels of management where speed and release cycles are a priority for them. The ramifications and risks, no matter how small, are real and very important to recognize.

Sanitizing user input before it reaches the SQL query is a great idea, and should always be considered. One of the ways to do that was covered in the first section of this post. Another way of dealing with this will be talked about later, which is to intercept the malicious requests with a web application firewall.

There are a few different ways to sanitize SQL statements in PHP, one of which is to use a built-in mysqli function mysqli_real_escape_string.

Basically how this function works is that you wrap all the variables that are used to craft an SQL statement in this function. It will then escape any special characters that may be injected into the variable (i.e. through a crafted GET or POST) :

$sql = "SELECT * from table where value = '".mysql_real_escape_string($variable)."';";

Pretty straightforward, right? You are just wrapping all variables into that function. Now some would argue that this isn’t the most secure way to sanitize input. Depending on your scenario and what the whole picture of your application is, it might be true.

Another option would be to use the PHP function bind_param. This is widely regarded as a better option as it binds parameters to prepared SQL statements :

$db = connect_db();
$stmt = $db->prepare('UPDATE table set column = ? where other_column = ? limit 1;');
$stmt->bind_param('ss', $variable1, $variable2);
$stmt->execute();

The bind_param function is much more preferable because it offers a lower chance of you being able to escape the mysqli_real_escape_string function to craft a malicious query.

Web Application Firewall

The last thing I would like to talk about here is an additional layer of security. The Web Application Firewall is never intended to be the only line of defense from malicious users. Its intended to complement a concise strategy of best practices in security development.

What the web application firewall will do is sit as a layer between the web service and PHP. WAFs are designed to detect and block what it would consider a malicious attempt to interact with code. When the WAF blocks the malicious request, it typically will return a 400 or 500 series error without ever letting the request reach PHP for it to be processed.

This is great, however most WAF systems are signature based and though they will block the baseline attempts to inject queries, scripts or code, they may not be able to detect and block more complicated and sophisticated attempts.

One of the more interesting and promising WAF systems we have been working with is a system called Naxsi. The main reason why Naxsi sounds promising is that it doesn’t rely on extensive rule signatures to detect “known” attacks (like systems such as mod security. Instead Naxsi detects unexpected characters in the HTTP requests/arguments. It uses a scoring system where the more unusual characters are embedded in a request, it increases the score and increases the likelihood of a block.

2015/06/22 10:24:28 [error] 32308#0: *259013 NAXSI_FMT: 
ip=x.x.x.x&server=www.domain.com&uri=/whatever/path/is/requested&learning=0&vers=0.54&total_processed=6&total_blocked=2&block=1&cscore0=$RFI&score0=8&zone0=BODY&id0=1101&var_name0=variable_passed, 
client: x.x.x.x, server: domain.com, request: "POST /whatever/path/is/requested HTTP/1.0", host: "www.domain.com", referrer: "https://www.domain.com/whatever"

For most scenarios, integrating a system like Naxsi will require setting Naxsi in “learning” mode where it will log all requests and “blocks”. Going through the site and performing legitimate tasks and user inputs will likely generate some false positives on the first round of testing. You will have to whitelist some of the requests in order to ensure that legitimate traffic is not inadvertently blocked.

You can also pair Naxsi with other security services such as Fail2ban which will dynamically create iptables (firewall) rules to block offenders after an attempted attack is made.

I hope this helps!