Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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:
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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." 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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 look for a specific file type in a local or remote server directory using PHP.

721 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