SQL Injection Series: Prevention from SQL Injection

SQL Injection Series

·                 -         Introduction

·         SQL Injection Mechanisms

·         Impact of SQL Injection

·         Exploitation of SQL Injection Techniques

·         Test/Detect SQL Injection

·         Prevention from SQL Injection

This article is focused on providing clear, simple, actionable guidance for preventing SQL Injection flaws in your applications.A successful SQL injection attack enables a malicious user to execute commands in your application's database by using the privileges granted to your application's login. The problem is more severe if your application uses an over-privileged account to connect to the database.

 Below are the points that we have to keep in mind from the prevention of SQL Injection
 Prepared Statements (Parameterized Queries)
The use of prepared statements (aka parameterized queries) is how all developers should first be taught how to write database queries. They are simple to write, and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.
Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. In the safe example below, if an attacker were to enter the userID of tom' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string tom' or '1'='1
Validating: Checking User Input: 
Assume all user-submitted data is evil and validate and sanitize everything.
Let's take a look at an example.
Say we have an input area in our form like this:

<input type="text" id="my-zipcode" name="my-zipcode" maxlength="5" />

Just like that, we've told the browser to only allow up to five characters of input, but there's no limitation on what characters they can input. They could enter "11221" or "eval(". If we're saving to the database, there's no way we want to give the user unrestricted write access.
This is where validation plays a role. When processing the form, we'll write code to check each field for its proper data type. If it's not of the proper data type, we'll discard it. For instance, to check "my-zipcode" field, we might do something like this:

• $safe_zipcode = intval( $_POST['my-zipcode'] );
• if ( ! $safe_zipcode )
• $safe_zipcode = '';
• if ( strlen( $safe_zipcode ) > 5 )
• $safe_zipcode = substr( $safe_zipcode, 0, 5 );
• update_post_meta( $post->ID, 'my_zipcode', $safe_zipcode );

Since the `maxlength` attribute is only enforced by the browser, we still need to validate the length of the input on the server. If we don't, an attacker could manually submit a form with a longer value.
The intval() function casts user input as an integer, and defaults to zero if the input was a non-numeric value. We then check to see if the value ended up as zero. If it did, we'll save an empty value to the database. Otherwise, we'll save the properly validated zipcode.
Sanitizing: Cleaning User Input
Input sanitization describes cleansing and scrubbing user input to prevent it from jumping the fence and exploiting security holes.Sanitization is a bit more liberal of an approach to accepting user data. We can fall back to using these methods when there's a range of acceptable input.
For instance, if we had a form field like this: <input type="text" id="title" name="title" />
We could sanitize the data with the sanitize_text_field() function: $title = sanitize_text_field( $_POST['title'] );
update_post_meta( $post->ID, 'title', $title );
Behinds the scenes, the function does the following:

• Checks for invalid UTF-8
• Converts single < characters to entity
• Strips all tags
• Remove line breaks, tabs and extra white space
• Strip octets

The sanitize_*() class of helper functions are super nice for us, as they ensure we're ending up with safe data and require minimal effort on our part:

• sanitize_email()
• sanitize_file_name()
• sanitize_html_class()
• sanitize_key()
• sanitize_meta()
• sanitize_mime_type()
• sanitize_option()
• sanitize_sql_orderby()
• sanitize_text_field()
• sanitize_title()
• sanitize_title_for_query()
• sanitize_title_with_dashes()
• sanitize_user()

Escaping strings
Escaping is securing output. This is done to prevent attacks and also to make sure that the data is displayed the way the user expects it to be.
Escaping converts the special HTML characters to HTML entities so that they are displayed, instead of being executed.
Example: Facebook escapes the chat messages while displaying them. To make sure that users don’t run code on each other’s computer.
esc_html()
This functions escapes HTML specific characters. Example code:
       <?php
        echo esc_html("<html>HTML</html>"); //Output "&lt;html&gt;HTML&lt;/html&gt;"
Use a Least-Privileged Database Account:
Your application should connect to the database by using a least-privileged account. If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, when you use SQL Server authentication, the corresponding SQL Server login should be restricted by permissions in the database.
Firewall:
Consider a web application firewall (WAF) – either software or appliance based – to help filter out malicious data. We have to use WAF that offers sophisticated application layer controls, including a collection of pre-defined, customer-configurable Web application firewall rules that enable deep packet inspection of HTTP/S request/response and payload analysis that can identify and protect against attacks such as SQL Injections