SQL injection

Description

This script is possibly vulnerable to SQL Injection attacks.

SQL injection is a vulnerability that allows an attacker to alter backend SQL statements by manipulating the user input. An SQL injection occurs when web applications accept user input that is directly placed into a SQL statement and doesn't properly filter out dangerous characters.

This is one of the most common application layer attacks currently being used on the Internet. Despite the fact that it is relatively easy to protect against, there is a large number of web applications vulnerable.

ShareShare on FacebookTweet about this on TwitterShare on Google+

Detailed Information
<i>Quote from SQL Injection Attacks by Example - http://www.unixwiz.net/techtips/sql-injection.html</i> <h3>SQL injection mitigations</h3> <p>We believe that web application developers often simply do not think about "surprise inputs", but security people do (including the bad guys), so there are three broad approaches that can be applied here.</p> <h3>Sanitize the input</h3> <p>It's absolutely vital to sanitize user inputs to insure that they do not contain dangerous codes, whether to the SQL server or to HTML itself. One's first idea is to strip out "bad stuff", such as quotes or semicolons or escapes, but this is a misguided attempt. Though it's easy to point out some dangerous characters, it's harder to point to all of them.</p> <p>The language of the web is full of special characters and strange markup (including alternate ways of representing the same characters), and efforts to authoritatively identify all "bad stuff" are unlikely to be successful.</p> <p>Instead, rather than "remove known bad data", it's better to "remove everything but known good data": this distinction is crucial. Since - in our example - an email address can contain only these characters: </p> <code><pre> abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789 @.-_+ </pre></code> <p>There is really no benefit in allowing characters that could not be valid, and rejecting them early - presumably with an error message - not only helps forestall SQL Injection, but also catches mere typos early rather than stores them into the database. </p> <p>Be aware that "sanitizing the input" doesn't mean merely "remove the quotes", because even "regular" characters can be troublesome. In an example where an integer ID value is being compared against the user input (say, a numeric PIN): </p> <code><pre> SELECT fieldlist FROM table WHERE id = 23 OR 1=1; -- Boom! Always matches! </pre></code> <p>In practice, however, this approach is highly limited because there are so few fields for which it's possible to outright exclude many of the dangerous characters. For "dates" or "email addresses" or "integers" it may have merit, but for any kind of real application, one simply cannot avoid the other mitigations. </p> <h3>Escape/Quotesafe the input</h3> <p>Even if one might be able to sanitize a phone number or email address, one cannot take this approach with a "name" field lest one wishes to exclude the likes of Bill O'Reilly from one's application: a quote is simply a valid character for this field.</p> <p>One includes an actual single quote in an SQL string by putting two of them together, so this suggests the obvious - but wrong! - technique of preprocessing every string to replicate the single quotes:</p> <code><pre> SELECT fieldlist FROM customers WHERE name = 'Bill O''Reilly'; -- works OK </pre></code> <p>However, this naive approach can be beaten because most databases support other string escape mechanisms. MySQL, for instance, also permits ' to escape a quote, so after input of '; DROP TABLE users; -- is "protected" by doubling the quotes, we get: </p> <code><pre> SELECT fieldlist FROM customers WHERE name = '''; DROP TABLE users; --'; -- Boom! </pre></code> <p>The expression ''' is a complete string (containing just one single quote), and the usual SQL shenanigans follow. It doesn't stop with backslashes either: there is Unicode, other encodings, and parsing oddities all hiding in the weeds to trip up the application designer.</p> <p>Getting quotes right is notoriously difficult, which is why many database interface languages provide a function that does it for you. When the same internal code is used for "string quoting" and "string parsing", it's much more likely that the process will be done properly and safely.</p> <p>Some examples are the MySQL function <b>mysql_real_escape_string()</b> and perl DBD method <b>$dbh->quote($value)</b>. These methods must be used. </p> <h3>Use bound parameters (the PREPARE statement)</h3> <p>Though quotesafing is a good mechanism, we're still in the area of "considering user input as SQL", and a much better approach exists: bound parameters, which are supported by essentially all database programming interfaces. In this technique, an SQL statement string is created with placeholders - a question mark for each parameter - and it's compiled ("prepared", in SQL parlance) into an internal form. Later, this prepared query is "executed" with a list of parameters: </p> <b>Example in perl</b> <code><pre> $sth = $dbh->prepare("SELECT email, userid FROM members WHERE email = ?;"); $sth->execute($email); </pre></code> <p>Thanks to Stefan Wagner, this demonstrates bound parameters in Java: </p> <b>Insecure version</b> <code><pre> Statement s = connection.createStatement(); ResultSet rs = s.executeQuery("SELECT email FROM member WHERE name = " + formField); // *boom* </pre></code> <p><b>Secure version</b></p> <code><pre> PreparedStatement ps = connection.prepareStatement( "SELECT email FROM member WHERE name = ?"); ps.setString(1, formField); ResultSet rs = ps.executeQuery(); </pre></code> <p>Here, $email is the data obtained from the user's form, and it is passed as positional parameter #1 (the first question mark), and at no point do the contents of this variable have anything to do with SQL statement parsing. Quotes, semicolons, backslashes, SQL comment notation - none of this has any impact, because it's "just data". There simply is nothing to subvert, so the application is be largely immune to SQL injection attacks.</p> <p>There also may be some performance benefits if this prepared query is reused multiple times (it only has to be parsed once), but this is minor compared to the enormous security benefits. This is probably the single most important step one can take to secure a web application. </p> <h3>Limit database permissions and segregate users</h3> <p>In the case at hand, we observed just two interactions that are made not in the context of a logged-in user: "log in" and "send me password". The web application ought to use a database connection with the most limited rights possible: query-only access to the members table, and no access to any other table.</p> <p>The effect here is that even a "successful" SQL injection attack is going to have much more limited success. Here, we'd not have been able to do the UPDATE request that ultimately granted us access, so we'd have had to resort to other avenues.</p> <p>Once the web application determined that a set of valid credentials had been passed via the login form, it would then switch that session to a database connection with more rights.</p> <p>It should go almost without saying that sa rights should never be used for any web-based application.</p> <h3>Use stored procedures for database access</h3> <p>When the database server supports them, use stored procedures for performing access on the application's behalf, which can eliminate SQL entirely (assuming the stored procedures themselves are written properly). </p> <p>By encapsulating the rules for a certain action - query, update, delete, etc. - into a single procedure, it can be tested and documented on a standalone basis and business rules enforced (for instance, the "add new order" procedure might reject that order if the customer were over his credit limit). </p> <p>For simple queries this might be only a minor benefit, but as the operations become more complicated (or are used in more than one place), having a single definition for the operation means it's going to be more robust and easier to maintain. </p> <p><i>Note: </i>it's always possible to write a stored procedure that itself constructs a query dynamically: this provides no protection against SQL Injection - it's only proper binding with prepare/execute or direct SQL statements with bound variables that provide this protection. </p> <h3>Isolate the webserver</h3> <p>Even having taken all these mitigation steps, it's nevertheless still possible to miss something and leave the server open to compromise. One ought to design the network infrastructure to assume that the bad guy will have full administrator access to the machine, and then attempt to limit how that can be leveraged to compromise other things. </p> <p>For instance, putting the machine in a DMZ with extremely limited pinholes "inside" the network means that even getting complete control of the webserver doesn't automatically grant full access to everything else. This won't stop everything, of course, but it makes it a lot harder. </p> <h3>Configure error reporting</h3> <p>The default error reporting for some frameworks includes developer debugging information, and this cannot be shown to outside users. Imagine how much easier a time it makes for an attacker if the full query is shown, pointing to the syntax error involved. </p> <p>This information is useful to developers, but it should be restricted - if possible - to just internal users. </p>

Impact
An attacker may execute arbitrary SQL statements on the vulnerable system. This may compromise the integrity of your database and/or expose sensitive information. [break][break]Depending on the back-end database in use, SQL injection vulnerabilities lead to varying levels of data/system access for the attacker. It may be possible to not only manipulate existing queries, but to UNION in arbitrary data, use subselects, or append additional queries. In some cases, it may be possible to read in or write out to files, or to execute shell commands on the underlying operating system.[break][break]Certain SQL Servers such as Microsoft SQL Server contain stored and extended procedures (database server functions). If an attacker can obtain access to these procedures it may be possible to compromise the entire machine.

Recommendation
Your script should filter metacharacters from user input.
Check detailed information for more information about fixing this vulnerability.

References
Acunetix SQL Injection Attack
VIDEO: SQL Injection tutorial
OWASP Injection Flaws
How to check for SQL injection vulnerabilities
SQL Injection Walkthrough
OWASP PHP Top 5