Till this point we have gathered enough information about how to exploit a website with SQL Injection attack and its related security threat known as Blind SQL Injection attack. Though both the attacks are very similar to each other but are used in different situations. Time Based SQL Injection is also one of the SQL Injection which is used when SQL-based website doesn’t show any kind of SQL error when various SQL Injection attacks are done in order to discover the vulnerabilities in a website. Now how to prevent SQL Injection attacks on SQL-based websites, we will make some of the well known and most used prevention methods against SQL Injection. This will reduce the maximum number of SQL based attacks on any SQL-based websites.
We’ll describe some of the methods to prevent SQL Injection:
To prevent SQL Injection, one has to validate all his input fields and sanitize them against vulnerable attempts.
Check the expected data type of input field, if the expected data is of any defined type for example is_numeric().
is_numeric – checks whether a variable is a number or a numeric string.
If the expected data type is numeric then use ‘intval’ to convert the input numeric into number format.
$id = intval($_GET['id']);
This is the type of input data we got from random action by the user.
Now let’s understand it by an example, what intval can do for us.
bool is_numeric ($var)
The above statement returns true if the value is number else false.
<?php echo intval(’23’); ?> // This php statement returns 23
<?php echo intval(array(‘-23’)); ?> // This php statement returns -23
If the array is empty it returns 0 else 1.
<?php echo intval(array()); ?> // This php statement returns 0
<?php echo intval(array(‘pen’)); ?> // This php statement returns 1
Remember The maximum value depends on the system. 32 bit systems have a maximum signed integer range of -2147483648 to 2147483647. So for example on such a system, intval(‘1000000000000’) will return 2147483647. The maximum signed integer value for 64 bit systems is 9223372036854775807.
Database layer doesn’t support parameterized statement, so you an use mysql_real_escape_string() to escape special characters. This can help in input fields validations. So an attacker can’t breach the secure wall of your website.
Prepared statements are the most powerful and recommended defense mechanism to prevent SQL Injection attacks.
These statements are all known as parameterized statements so, they can run the query more faster and securely.
These statements can be parsed one by the application server and then be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query.
The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.
Why Prepared Statements are used?
By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.
How prepared statements work?
The prepared statement execution consists of two stages: prepare and execute.
Separate SQL logic and data
To prevent SQL Injection, prepared statements help to sanitize the vulnerable attempts.
- Prepare: At the prepare stage the database parses the query and allocates space for the parameters.
At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.
The MySQL server supports using anonymous, positional placeholder with ?.
- Bind Parameters
Once the prepared statements are made, pass the data to the parameters. Whatever is passed it will be considered as Data by the application database server, because application avoids repeating the analyze/compile/optimize cycle. This surely prevents SQL Injection.
Example #1 Repeated inserts using prepared statements
This example performs an INSERT query by substituting a name and a value for the named placeholders.
<?php $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)"); $stmt->bindParam(':name', $name); $stmt->bindParam(':value', $value); // insert one row $name = 'one'; $value = 1; $stmt->execute(); // insert another row with different values $name = 'two'; $value = 2;
Example #2 Repeated inserts using prepared statements
This example performs an INSERT query by substituting a name and a value for the positional ? placeholders.
<?php $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)"); $stmt->bindParam(1, $name); $stmt->bindParam(2, $value); // insert one row $name = 'one'; $value = 1; $stmt->execute(); // insert another row with different values $name = 'two'; $value = 2; $stmt->execute(); ?>
To prevent SQL Injection, always minimize the potential damage of a successful SQL injection attack by minimizing the privileges assigned to every database account in your environment.
If the database user account is assigned reading privileges, then this user should be assigned read permissions only.
Never give root access to any database user account, this could be dangerous in a way that your website could be compromised by the malicious user or the result could be in the denial of service.