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

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?
LVL 32
Who is Participating?
Ray PaseurConnect With a Mentor Commented:
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:
Dave BaldwinFixer of ProblemsCommented:
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'.
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?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

DrDamnitAuthor Commented:
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?
Ray PaseurCommented:
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.
GaryConnect With a Mentor Commented:
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.
Ray PaseurCommented:
... only a worry where you are allowing unmonitored inserts into the db ...
For example, in the sixty million plus, and counting, WordPress sites...
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.