Faraz

Ahmed

PHP Developer & IT Business Analyst


Preventing SQL and XSS Injections

July 26, 2013By Faraz Ahmed4 Comments

I’ve seen a lot of custom coded PHP applications, which have done it all functionality wise, but when it came to validating and securing the incoming data from the input fields before saving them into the database, most of them fell short.

A lot of developers I know don’t know what SQL or XSS Injection is, and those who do know about the terms don’t know how the injection happens and how to protect your website against them. This post is about how you can secure both types of injections using just one method.

SQL Injection is basically entering a SQL statement of your choice in an input field in a way that it gets executed by the website when read. If done successfully, the hacker can basically have access to your whole database, so you can imagine how dangerous it is.

You might not have witnessed SQL Injection ever on your applications, not because you’ve secured the applications, but because of PHP’s ‘magic_quotes_gpc’ feature, which is enabled by default in PHP versions prior to 5.3 and escapes the incoming data automatically. However it is strongly recommended not to rely on this setting to secure your applications from SQL Injections. The feature has been deprecated as of PHP 5.3 and removed as of PHP 5.4. So if you are planning to upgrade your PHP version, make sure that you first secure all your input, otherwise your application will become vulnerable to SQL Injection. I’ve always coded my PHP applications with this feature disabled, as I’ve always liked having complete control over my code instead of relying on the outside factors to secure my code.

The best way to prevent SQL Injection is to use PDO and prepared statements. That way you won’t have to do anything extra to prevent SQL Injection. However it will not save you from XSS Injections. If your application is using MySQL (which is deprecated in PHP 5.5 and removed in the future), then you might already be using ‘mysql_real_escape_string’ for escaping the data. However it will also not save you from XSS Injections.

So what exactly is XSS Injection? XSS, also known as cross site scripting, is a way of adding client side javascript or HTML code into your website. If there is an input field on your application which takes data from the user and shows it on one of your public website pages (Ex: comments page), and it is not secured, then the hacker can enter any javascript in the input field and it will get executed on the page where the content of the input field is supposed to show. The hacker this way can redirect your users to his own website, and can also steal cookies of your users to login as them and steal their information.

So how can you protect your applications from the above injections? First of all, if you are not using PDO in your applications, you should start using it right away instead of MySQL, because MySQL extension will be removed from the future PHP versions.

Now create a following function in a global file of your PHP application:

function sanitize ($input) {
return htmlentities ($input, ENT_QUOTES, "UTF-8" );
}

Now pass all your input fields as an argument to the above function before entering them in the database. What the above function does is that it converts all the HTML characters (including single and double quotes) into their equivalent HTML entities, so ’<b>bold</b>’ will be converted into ‘&lt;b&gt;bold&lt;/b&gt;

When the above encoded data is stored in the database and shown on the page, it will be shown as ‘<b>bold</b>’ and not as bold. Notice the difference?

Also all the single and double quotes will be converted into ‘#039;’ and ‘&quot;’ respectively, which will protect from SQL Injection, as single and double quotes will be entered as text in the database and won’t have any special meaning in the SQL statement they are being passed to.

I hope you enjoyed the article and learned something from it. Please let me know your feedback in the comments section below.






This article has 4 comments
  1. Sajjad Aslam
    July 27, 2013

    We cant say that using PreparedStatement prevents us from SQL injection attacks. The primary reason of SQL injection attacks is the use of dynamic queries. Even if we are using PreparedStatement and if we introduce dynamic queries inside that PreparedStatement that will not prevent SQL injection attacks. Following is an example of SQL injection attacks using PreparedStatement in java.

    int employeeID = 90;
    int status=1;
    String query = “SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID=” + employeeID + ” AND STATUS=” + status ;
    PrepareStatement ps = conn.prepareStatement(query).

    After having a look at the code, one can notice that I am using dynamic query(query made by string concatenation). Even I am using PreparedStatement, this piece of code will not prevent us from SQL injection attacks.

    The solution to this problem is to use static/Parameterized queries like this.

    int employeeID = 90;
    int status=1;
    String query = “SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID = ? AND STATUS=?”;
    PrepareStatement ps = conn.prepareStatement(query).
    ps.setInt(1, employeeID);
    ps.setInt(2, status);

    This time I used static/Parameterized query (Query with out string concatenation) in PreparedStatement .

    This code will now prevent SQL injection attack.

    We cant simple say use of PreparedStatement will prevent SQL injection attacks. The whole point is, if we want to prevent SQL injection attacks, use Static/Parameterized queries with PreparedStatemnent.

    • Faraz Ahmed
      July 27, 2013

      Thanks for your comment, Sajjad. You are 100% correct. However prepared statement is just another for word for parameterized statement. Without parameters, there is no point of using it.

      • Sajjad Aslam
        July 27, 2013

        Faraz, PreparedStatement can be used as parameterized statement but they wont prevent you to be used in non-parameterized manner.

        String query = “SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID=” + employeeID + ” AND STATUS=” + status ;

        PreparedStatement ps = conn.prepareStatement(query);
        ps.execute();

        Now in the above code I didn’t use parameterized query and also PreparedStatement wont stop me doing this. The above code is 100% valid but logically is not correct. This code will not prevent SQL injection attacks.

        The point is why I am explaining it in detail. It is because even some experienced developers wont know the fact and they sometimes claims that only using PreparedStatement will solve the problem which is not correct. PreparedStatement will never prevent SQL injection attacks without the use of parameterized query. So we have to use parameterized query in conjunction with PreparedStatement in order to prevent SQL injection attack.

        Thanks

        • Faraz Ahmed
          July 27, 2013

          I agree. Most programmers would miss out the point about parameterized query. Thanks for the detailed explanation! I am sure this will help anyone who reads it.


Leave a Reply