Skip to main content
If you take user input through a webpage and insert it into a MySQL database, there's a chance that you have left yourself wide open for a security issue known as SQL Injection. This lesson will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.
Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a MySQL statement that you will unknowingly run on your database.
Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the example below, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars - modify these rules as needed.
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM users 
                          WHERE username=$matches[0]");
}
 else 
{
   echo "username not accepted";
}
To demonstrate the problem, consider this excerpt:
// supposed input
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");
The function call is supposed to retrieve a record from the users table, where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster: the injected DELETE query removes all records from users.
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails.
However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.

Preventing SQL Injection:

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.
if (get_magic_quotes_gpc()) 
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");

The LIKE Quandary:

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied % and _ characters to literals. Use addcslashes(), a function that let's you specify a character range to escape.
$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

Comments

Popular posts from this blog

The Windows Firewall with Advanced Security is a firewall that runs on the Windows Server 2012 and is turned on by default. The Firewall settings within Windows Server 2012 are managed from within the  Windows Firewall Microsoft Management Console . To set Firewall settings perform the following steps − Step 1  − Click on the Server Manager from the task bar → Click the Tools menu and select Windows Firewall with Advanced Security. Step 2  − To see the current configuration settings by selecting  Windows Firewall Properties  from the MMC. This  allows access to modify the settings  for each of the three firewall profiles, which are –  Domain, Private and Public  and IPsec settings. Step 3  − Applying custom rules, which will include the following two steps − Select either  Inbound Rules  or  Outbound Rules  under  Windows Firewall with Advanced Security  on the left side of the management console...
The IIS ( Internet Information Services ) is facing internet all the time. So, it is important to follow some rules in order to minimize the risk of being hacked or having any other security issues. The first rule is to take all the updates of the system regularly. The second one is to create different application polls to this, which can be done by following the steps shown below. Step 1  − You have to go to: Server Manager → Internet Information Services(IIS) Manager → Application Pulls. Step 2  − Click “Sites” → Right Click “Default Website” → Manage Website → Advance Settings. Step 3  − Select the Default Pools. Step 4  − Disable the OPTIONS method, this can be done by following the path – Server Manager → Internet Information Services (IIS) Manager → Request Filtering. Step 5  − In the action pane, select "Deny Verb" → Insert ‘OPTIONS’ in the Verb → OK. Step 6  − Enable Dynamic IP Restrictions blocks by going to – IIS Manager → Double...
In this chapter, we will see how to configure WSUS and tune it. The following steps should be followed for configuring it. Step 1  − When you open it for the first time, you should do it by going to “Server Manager” → Tools → Windows Server Update Services, then a Configuration wizard will be opened and then click → Next. Step 2  − Click “Start Connecting” → Wait until the green bar is full and then → Next. Step 3  − Check the box for which the updates want to be taken, I did for English and then → Next. Step 4  − Check the box for all the products which you want to update. It is just for Microsoft products and it is recommended to include all the products related to Microsoft and then → Next. Step 5  − Choose the classification updated to be downloaded, if you have a very good internet speed, then check all the boxes, otherwise just check “Critical Updates”. Step 6  − Now we should schedule the updates which I will recommend to do it a...