Solved

What's the most elegant way to sanitize query variables to prevent XSS, etc...?

Posted on 2014-09-17
8
146 Views
Last Modified: 2014-09-24
From stripslashes() / addslashes(), to PDO, and sometimes using htmlentities, there are a thousand ways to sanitize data before allowing the user input into your SQL queries. What's the most elegant, current, best practice you are using?
0
Comment
Question by:DrDamnit
8 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 40328890
XSS stands for "Cross Site Scripting".  With SQL, the problem is normally "SQL Injection" where someone put SQL code in your form variables.  For MySQL, you 'should' be using mysqli::real_escape_string -- mysqli_real_escape_string http://us1.php.net/manual/en/mysqli.real-escape-string.php instead of stripslashes() / addslashes().  In addition, I have been using substr() to limit the size of POSTs like this.  

if (!isset($_POST["Name"]))  $Name = ''; else $Name = substr($_POST["Name"],0,64);

Open in new window

I wouldn't call any of this 'elegant'.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40328903
If you are using PDO then you don't need to worry about it as long as you are using named placeholders, though as Dave asked which do you mean - XSS or SQL injection?
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 40328960
There is a security mantra: "Accept Only Known Good Values."  Note that this is not exactly the opposite of "Reject Known Bad Values" because in the former case you get to create the tests for good values and in the latter case, the bad guys get to create the values and you have to guess about how to handle things.

Example: If you're expecting a positive integer, use a regular expression or filter to remove everything except the numbers 0-9, then compare the original and filtered data.  If they do not match, you did not get a positive integer from the data source.  

As a general rule, I write one filter method for each external data element.  I take anything that passes the basic filters and use MySQLi's "real escape" method.  That takes care of the quotes, nulls, etc., that may be legitimate parts of the data.  When the information is written into the database, you're recording exactly what the data contains, no more, no less.  Addslashes() really doesn't make sense any more.

There is another security mantra: "Filter Input, Escape Output."

When you get ready to send the data to the client browser, you escape the data by using HTMLentities() to prevent malicious Javascript from triggering an action on the client browser.

PHP has a section on Security.  Good reading!
http://php.net/manual/en/security.php

If you want to hear it from the guy who wrote the book (literally) make a Google search for Shiflett XSS and you'll find plenty of information.  Chris did most of his security work in the early 2000's and it's still among the best body of collected PHP security information anywhere.

See also:
https://www.owasp.org/index.php/XSS_Filter_Evasion_Cheat_Sheet
https://www.owasp.org/index.php/SQL_Injection
https://www.owasp.org/index.php/CSRF
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 40329346
This question started out as a XSS question, but then I realized I was talking more about SQL Injection, but accidentally left the XSS in the question title. (Although, it is possible to store an XSS attack in a remote database using SQL Injection so that whenever a page is loaded, that server executes an XSS attack. Ray alluded to this in his comment about htmlentities...).

But, yeah, I meant SQL Injection - that's the root of the problems I am considering.

My current methods are:
Using $mysqli::real_escape_string() for everything, and when there are known items (id's, for example) type-checking for valid inputs. I use prepared statements in production as well because it just... seems... sensible to do things that way. Plus, you cannot put garbage in a prepared statement. (A string cannot go where an integer should be, for example).

One problem I have with PDO is that there is no way to view / (read: var_dump() / print_r() ) the rendered SQL query. Before I started using PDO, I always built queries using sprintf(), which had the convenient debugging ability to dump the SQL query to a log or the browser (in a dev environment) so I could copy / paste it into Workbench or the MySQL CLI and run it to see what I got... or in most cases, why it was broken). As sort of a side question, is there a way to do that with PDO?

Since i periodically review coding practices (especially in the security area), I was looking at how we do things, and my thoughts are exactly what Dave wrote above: "I wouldn't call any of this elegant."

OWASP.org is a very cool site that I stumbled across years ago studying for the CEH exam. (A cert that I have not kept current because it didn't fit well with my business model). So, while I can invent ways to break into a site for pen testing, I got to thinking about it: there must be a better way to prevent these vulnerabilities than a series of functions (or a specialized class) that would handle data for a project.

Thus... I was looking for an "elegant" way to do this.

From all of your answers, it appears that there isn't much of an elegant way to do this (yet). So, it seems like I will just keep on keepin' on for this?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329417
is there a way to do that with PDO?
Nope.  Here's the best explanation I can muster.

When you send a query string to the SQL engine you're sending a computer program.  The program is made up of the SQL statements and the data, that may have come from external sources.  The great PHP vulnerability was not the query string, it was the use of external data sources in the contents of the computer program.  PDO tries to plug up that vulnerability by separating the SQL statements from the external data.  They are sent separately, so there is no way that an ignorant PHP programmer can f* up the SQL operations.  You can still screw up your database by writing unfortunate queries or storing the wrong data, but you have less opportunity to cause the query engine to fail because the SQL query program is taken out of your hands by PDO.
0
 
LVL 58

Assisted Solution

by:Gary
Gary earned 200 total points
ID: 40329425
One problem I have with PDO is that there is no way to view ... the rendered SQL query
You can dump the sql statement and you can dump the associated array.
One of the main reasons for echoing the sql statement was to check for misplaced apostrophes etc, that doesn't (read cannot) happen with PDO.
So yes you cannot see the end resulting sql because as Ray as said above their is no sql with all the values filled in but if you have the sql and you have the values then what is the problem.
The question started out about  SQL injection (nay XSS) - PDO eliminates that without any work on your side to sanitize the data.
The only thing to worry about is if you were echoing out data from the db and that data contained the likes of JS so you would need to implement htmlentities to prevent that - but that is only a worry where you are allowing unmonitored inserts into the db which should always be scrutinized.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329436
... only a worry where you are allowing unmonitored inserts into the db ...
For example, in the sixty million plus, and counting, WordPress sites...
0
 
LVL 58

Expert Comment

by:Gary
ID: 40329439
As the article title So Where's The Money - in the hands of the thousands of hackers that hack WP sites every day for a living
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now