Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2014-09-17
Medium Priority
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?
Question by:DrDamnit
LVL 84

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'.
LVL 58

Expert Comment

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?
LVL 111

Accepted Solution

Ray Paseur earned 1200 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!

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:
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 32

Author Comment

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?
LVL 111

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.
LVL 58

Assisted Solution

Gary earned 800 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.
LVL 111

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...
LVL 58

Expert Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

564 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