SQL Injection: How To Prevent Security Flaws In PHP / MySQL

\r\n
\r\n
\r\n
\r\n
\r\n
What is SQL Injection\r\nMost new web developers have heard of SQL injection attacks, but not very many know that it is fairly easy to prevent an attacker from gaining access to your data by filtering out the vulnerabilities using MySQL extensions found in PHP. An SQL injection attack occurs when a hacker or cracker (a malicious hacker) attempts to dump the data in a database table in a database-driven web site. In an unprotected and vulnerable site, this is pretty easy to do.\r\n\r\nSQL injection is a common vulnerability that is the result of lax input validation. Unlike cross-site scripting vulnerabilities that are ultimately directed at your site’s visitors, SQL injection is an attack on the site itself, in particular its database.\r\nThe goal of SQL injection is to insert arbitrary data, most often a database query, into a string that’s eventually executed by the database. The insidious query may attempt any number of actions, from retrieving alternate data, to modifying or removing information from the database.\r\n\r\nHow does SQL injection attack works\r\nIn order for an SQL injection attack to work, the site must use an unprotected SQL query that utilizes data submitted by a user to lookup something in a database table. The data could be from a search box, a login form or any type of query used to look up data using data input by user. It also means that querystring data used to query a database can create vulnerabilities.\r\nFor example:\r\n\r\nAn very simple unprotected query might look like this:\r\n\r\n

\r\n

\r\n

\r\n
SELECT * FROM items WHERE itemID = '$itemID'

\r\n

\r\n

Normally, you would expect a user to submit a username and password, which would be used to query the database table to see if the username and password exists. But what if someone used the following instead of a password?

\r\n

‘ OR ‘1′ = ‘1

\r\n

\r\n

\r\n

That would make the query used to look for the password look like this:

\r\n

\r\n
\r\n
SELECT * FROM items WHERE itemID = '' OR '1' = '1'

\r\n

\r\n

\r\n

\r\n

This would always return a True response and could literally display the entire table as the result for the query. This is a pretty scary thought if you are trying to keep your data secure. The problem with SQL injection is that a hacker does not have to know anything about your database or table structure.\r\n\r\nWhat if an error or some other issue caused your table structure to be exposed? Hackers are very good at forcing errors to occur that expose information that allows them to penetrate a site deeper. What if the following was entered in the password field?\r\n\r\n

\r\n

‘; drop table users;

\r\n

\r\n

How to prevent your database from SQL Injection attacks\r\nThere is a method for filtering the data that is used on the right side of the WHERE clause to look up a row in a database. The trick is to escape any characters that may be in the user input portion of the query that could lead to a successful attack.\r\n\r\nUse the following function to add backslashes to suspect characters and filter any data that is input by a user.\r\n\r\n

\r\n

function cleanQuery($string)\r\n{\r\n if(get_magic_quotes_gpc()) // prevents duplicate backslashes\r\n {\r\n  $string = stripslashes($string);\r\n }\r\n  if (phpversion() >= '4.3.0')\r\n  {\r\n   $string = mysql_real_escape_string($string);\r\n  }\r\nelse\r\n{\r\n $string = mysql_escape_string($string);\r\n}\r\nreturn $string;\r\n}\r\n\r\n// if you are using form data, use the function like this:\r\nif (isset($_POST['itemID'])) $itemID = cleanQuery($_POST['itemID']);\r\n\r\n// you can also filter the data as part of your query:\r\nSELECT * FROM items WHERE itemID = '". cleanQuery($itemID)."' "

\r\n

The first part looks to see if magic quotes is turned on. if so, it may have already added backslash escapes though a POST or GET method used to pass the data. If backslashes were added, they need to be removed prior to running it through the rest of the function.\r\n\r\nThe next part checks the PHP version. The built-in function that we want to use is called mysql_real_escape_string. This MySQL function only exists in PHP version 4.3.0 or newer. If you are using an older version of PHP, another MySQL function is used called mysql_escape_string.\r\n\r\nmysql_escape_string is not as effective as the newer mysql_real_escape_string. The newer version escapes the string according to the current character set. The character set is ignored by mysql_escape_string, which can leave some vulnerabilities ope for sophisticated hackers. If you find that you are using an older version of PHP and you are trying to protect sensitive data, you really should upgrade to a current version of either PHP 4 or PHP 5.\r\n\r\nSo what does mysql_real_escape_string do?\r\n\r\nThis PHP library function prepends backslashes to the following characters: \n, \r, \, \x00, \x1a, ‘ and “. The important part is that the single and double quotes are escaped, because these are the characters most likely to open up vulnerabilities.\r\n\r\nFor those who do not know what an escape is, it is a character that is pre-pended to another character. When a character is escaped, it is ignored by the database. In other words, it makes that character ineffective in a query. In the case of PHP, an escaped character is treated differently by the PHP parser. The standard escape character used by PHP and MySQL is the backslash.\r\n\r\nIn the case of the SQL query example used above, after running it through the routine, it now looks like this, which breaks the query :\r\n\r\n

\r\n

\r\n
SELECT * FROM items WHERE itemID = '\' OR \'1\' = \'1'

\r\n

\r\nThis method should stop the bulk of the SQL injection attacks, but crackers and hackers are very creative and are always finding new methods to break into systems. There are additional steps that can be taken to filter out certain words, such as drop, grant, union, etc., but using this method will strip these words from searches performed by you users. However, if you want to add another level of security and do not have an issue with certain words being deleted from queries, you can add the following just before if (phpversion() >= ‘4.3.0′).\r\n

$badWords = array("/delete/i", "/update/i","/union/i","/insert/i","/drop/i","/http/i","/--/i");\r\n$string = preg_replace($badWords, "", $string);

\r\nThis additional step should prevent a malicious attacker from damaging a database if they found a way to slip through. Just remember that is you take this additional step and you have a site where someone might search for a “plumbing union” or a “drop cloth”, those queries would not work as intended. If you are wondering what the trailing ‘i’ is following each word in the array, it is required to make the preg_replace replacements case insensitive. This wasn’t needed with eregi_replace, but that function has been deprecated in PHP 5.3.\r\n\r\nAnother important step that needs to be taken with any database is controlling user privileges. When setting up a MySQL user, you should never assign any more privileges than they actually need to accomplish the tasks that you allow on your site. Privileges are easily assigned and managed thought phpMyAdmin, which is found in the the control panel (cPanel, Plesk, etc.) for most hosting companies.\r\n\r\nUseful Links\r\n

http://en.wikipedia.org/wiki/SQL_injection\r\nhttp://www.learnphponline.com/securi…tion-mysql-php\r\nhttp://dev.mysql.com/tech-resources/…curity-ch3.pdf\r\nhttp://www.tizag.com/mysqlTutorial/m…-injection.php

Antivirus and anti-malware protection

\r\n
There are many different antivirus and anti-malware protection programs available, ranging in price from free, to several hundred dollars, depending upon their sophistication and scope of use. It is critically important that anyone that connects to the internet has adequate protection against possible infections by viruses, trojans, worms, and various malware that circulate so prolifically these days.\r\n\r\nThere is an adage that says, “If a little bit is good, then a lot is better”. However, in the case of virus protection, this is normally not true (in fact, in my experience, NEVER). Although it may be permissible to run more than one anti-malware protection program at a time, one should have only one antivirus program operating at any given time. There are reasons for this.\r\n\r\nTwo different AV programs will often conflict, seeing each other as a virus, because of the nature of their operation. Thus, when one program succeeds in stifling the activities of the other, a window of opportunity for an actual virus may be created. More often, each will manage to limit the effectiveness of the other, thus creating a weakness that can be exploited. “Loops” can be created, wherein two AV programs will endlessly fight each other for control of a given function, leaving that function effectively unprotected. More is NOT better!\r\n\r\nOne should select their protection carefully, giving thought to the particular sorts of risks they make themselves vulnerable to by their surfing style. As a member of many web professional forums, I prefer to make my selection, after hearing the recommendations of others. I also have found that for my purposes, there is no need to purchase such a program.\r\n\r\nThere are a number of very effective AV programs, written and maintained by reputable organizations, that can be downloaded and installed at no charge. I presently use Avast, which I feel to be on a par with Avira, in terms of effectiveness in the AV realm. Both also offer enhanced versions for purchase, and have products that specialize in other levels of protection. Kaspersky is another system that has an excellent track record for protection.\r\n\r\nMost such products offer a free evaluation period, up to a month, to see if their program does the job you want. I usually warn people away from such evaluation periods, however, simply because some such programs are difficult to remove completely (Symantec’s Norton is one that’s notoriously difficult to get rid of) from your system.\r\n\r\nThere are a few things that should be remembered, when searching for the best AV program for your system:\r\n
    \r\n
  • NO AV program is perfect! Some are better than others, but new exploits are released almost daily, and your protection is only as good as its relevancy. If it updates virus definitions weekly, then you may be vulnerable to new viruses for several days between updates.
  • \r\n

  • The sort of experts that are capable of developing protection against viruses have their doppelgangers on the other side of the coin… those that develop the viruses to begin with. Both are good at what they do, and at any given moment, one will be a step ahead of the other.
  • \r\n

  • Realtime scanning is an important feature to seek in your AV protection. Scanning emails and downloads is important, but viruses and malware can be activated by the simple act of clicking on a text link, an image or opening an attachment. Just entering a page can enable an infection, without sufficient protection.
  • \r\n

  • The best AV protection available cannot do the job alone. YOU have to take an active part in protecting your system. If you frequent poor reputation sites, hang out in “bad neighborhoods”, the infection of your computer is made much more probable, regardless of the AV protection you run.
  • \r\n

\r\nAV protection is only one aspect of protection. A reliable firewall and adequate anti-spyware protection are other important protections that should be considered. I suggest you investigate what security bloggers and forum members have to say about the AV protection they have used or are using. A satisfied customer is always a good reference. Seofast

\r\n

Solving ReportViewer Rendering Issue on IIS7

Solving ReportViewer Rendering Issue on IIS7\r\n\r\n\r\n\r\nApplies to:\r\n

    \r\n
  • Internet Information Services 7.0 (IIS7)
  • \r\n

  • Microsoft Report Viewer Redistributable 2005
  • \r\n

\r\nSymptoms:\r\n

    \r\n
  • Unable to render ReportViewer on ASP.NET Web pages while running on IIS7.
  • \r\n

  • You have no problem viewing your reports when running on debug mode with your Visual Studio 2005.
  • \r\n

  • You are able to view your reports on Report Manager but not able to view them on IIS7.
  • \r\n

  • You encounter JavaScript error when loading your report page with ReportViewer. Image buttons such as calendar appear as red ‘X’.
  • \r\n

\r\nCause:\r\n

    \r\n
  • When the ReportViewer control is added to Web Form (.aspx), the\r\nReserved.ReportViewerWebControl.axd httpHandler is added to System.Web section of the Web.Config file. In IIS7, it should be added under System.Webserver section.
  • \r\n

  • IIS7 Handler Mappings does not contain Reserved.ReportViewerWebControl.axd httpHandler, and therefore unable to render the ReportViewer elements needed by the JavaSript.
  • \r\n

\r\nResolution:\r\n

    \r\n
  • Open Internet Information Services (IIS) Manager and select your Web application.
  • \r\n

  • Under IIS area, double-click on Handler Mappings icon.
  • \r\n

  • At the Action pane on your right, click on Add Managed Handler.
  • \r\n

  • At the Add Managed Handler dialog, enter the following:\r\nRequest path: Reserved.ReportViewerWebControl.axd\r\nType: Microsoft.Reporting.WebForms.HttpHandler\r\nName: Reserved-ReportViewerWebControl-axd
  • \r\n

  • Click OK.
  • \r\n

\r\nReserved-ReportViewerWebControl-axd handler is now added to your Handler Mappings list. Notice that the following line has also been added to your Web.config file under the system.webserver’s handler section:\r\n

<add name="Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified" />

\r\nRun your report again.

How To Enable AJAX .NET Framework 3.5 on IIS7 Server

IIS versions before 7.0 did not require <handlers> for AJAX to work.\r\nIf you are installing AJAX on new IIS7 servers, you will have to make sure you have the following code in your web.config:\r\n

<!--  \r\n The system.webServer section is required for running ASP.NET AJAX under Internet \r\n Information Services 7.0.  It is not necessary for previous version of IIS. \r\n --> \r\n <system.webServer> \r\n <validation validateIntegratedModeConfiguration="false"/> \r\n <modules> \r\n <remove name="ScriptModule"/> \r\n <add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/> \r\n </modules> \r\n <handlers> \r\n <remove name="WebServiceHandlerFactory-Integrated"/> \r\n <remove name="ScriptHandlerFactory"/> \r\n <remove name="ScriptHandlerFactoryAppServices"/> \r\n <remove name="ScriptResource"/> \r\n <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/> \r\n <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/> \r\n <add name="ScriptResource" verb="GET,HEAD" path="ScriptResource.axd" preCondition="integratedMode" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/> \r\n </handlers> \r\n </system.webServer>

How To Fix ‘Microsoft.Jet.OLEDB.4.0′ error

\r\n

Problem:

\r\n

\r\n
Server Error in ‘/’ Application.

\r\n

——————————————————————–

\r\n

The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine.

\r\n

Description: An unhandled exception occurred during the execution of the current web request.

\r\n

Please review the stack trace for more information about the error and where it originated in the code.

\r\n

Exception Details: System.InvalidOperationException: The ‘Microsoft.Jet.OLEDB.4.0′

\r\n

provider is not registered on the local machine.

\r\nSolution:\r\n\r\nYou will get this error on Windows Server 2008 R2 or Windows 7 64 bit. To fix it, switch your Application Pool from Native 64 bit to 32 Bit more under Advanced Settings.\r\n\r\nSuggestion:\r\n\r\nIt is also suggested that you upgrade your application to new ACE OLEDB provider, you can download from here.

How To Fix overrideMode=”Deny” Error (HTTP Error 500.19)

HTTP Error 500.19 - Internal Server Error\r\nThe requested page cannot be accessed because the related configuration data for the page is invalid.\r\nConfig Error\r\nThis configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault="Deny"), or set explicitly by a location tag with overrideMode="Deny" or the legacy allowOverride="false".\r\n226:     <system.webServer>\r\n227:         <handlers>

\r\nIf you get the error above, you have to make a change in ApplicationHost.config file. To fix this error follow the steps as  below:\r\n

    \r\n
  • Open ApplicationHost.config file in notepad This file is located under C:\Windows\System32\inetsrv\config
  • \r\n

  • Search for <location path=”Default Web Site” overrideMode=”Deny”>
  • \r\n

  • Replace with: <location path=”Default Web Site” overrideMode=”Allow”>
  • \r\n

\r\nChange “Default Web Site”  with the name of your website.\r\n\r\nNOTE: On 64 bit Windows, if you are using 32 bit editor or File Manager, you will NOT be able to see any files in the config folder. Navigate using Windows Explorer or Notepad in native 64 bit mode.

How to Create Rewrite Rule in web.config

If you need a domain URL redirection from yourdomain.com to www.testdomain.com or vise versa, you can do something like this:\r\n\r\nPlace either of these (depending on what you’d like done. And edit to match your domain) inside the <system.webServer></system.webServer> tags in the web.config of the domain.\r\n

<rewrite><rules>\r\n<rule name=”Add WWW prefix” >\r\n<match url=”(.*)” ignoreCase=”true” />\r\n<conditions>\r\n<add input=”{HTTP_HOST}” pattern=”^testdomain\.com” />\r\n</conditions>\r\n<action type=”Redirect” url=”http://www.testdomain.com/{R:1}”\r\nredirectType=”Permanent” />\r\n</rule>

\r\n—————–\r\n

<rule name=”Remove WWW prefix” >\r\n<match url=”(.*)” ignoreCase=”true” />\r\n<conditions>\r\n<add input=”{HTTP_HOST}” pattern=”^www\.testdomain\.com” />\r\n</conditions>\r\n<action type=”Redirect” url=”http://yourdomain.com/{R:1}”\r\nredirectType=”Permanent” />\r\n</rule>\r\n</rules></rewrite>

\r\n

Note: This is applicable on websites hosted on IIS 7.0 or 7.5 (on Windows Server 2008).