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

Posted on 2014-09-17
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 83

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 110

Accepted Solution

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!

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:
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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 110

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

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …

735 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