Filtering User Input

APD Toronto
APD Toronto used Ask the Experts™
Hi Experts,

I have a textarea that the user needs to provide comments for a varchar(4000) MySQL field.

Using PHP, how can I filter(or better - escape) malicious or even naive input like a semicolon in a sentence.

For my particular scenario I cannot use any binding (at least I don't think I can), because I am updating up to 50 records with the same comment.

Actually, I know I can bind within my loop, but that would mean hitting the database up to 50 times
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Year 2008
Top Expert 2008
If you are executing something like:
UPDATE Shoes SET discount =? WHERE `color`=?

and you bind $discount='10%', $color='red', it doesn't matter if there are 100 pairs of red shoes or just 1 -- they will all be updated with a 10% discount.
Marco GasiFreelancer
Top Expert 2010
You can build an array of forbidden values and use it in str_replace. Suppose you want get rid of ';', '<', '>':

$comment = $_POST['comment'];
$forbidden_values = array(';', '<', '>');
$result = str_replace($forbidden_values, '', $comment);

Open in new window

You might be interested in learning about filter_input and sanitizing filters
Most Valuable Expert 2011
Top Expert 2016
Further to Hielo's comment, the SQL standard applies the query to every row of the database, except as restricted by the WHERE and LIMIT clause.  If you run a query that says DELETE FROM myTable, Poof!  All your data is gone.  This applies to any query that mutates rows, and also applies to SELECT queries without WHERE and LIMIT clauses -- all rows are inspected.  That can create a hidden performance bug, much like writing SELECT * instead of selecting the columns you need.

There is nothing wrong with a semicolon in user input, but there might be something wrong with the application design, if I am understanding this question correctly.  Here is what I think I might be getting from the question.

1. A user visits the web site and puts up to 4,000 characters of data into a textarea
2. When the form is submitted the server takes the textarea and puts its contents into more than one row of the database

If that's the case, the application violates the DRY principle.  A better design might be to put the contents of the textarea into a database table, get the key of the inserted row, and put this key into the related rows of the database.  That's using the relational part of a relational database correctly!

Marco's links for filter and sanitize are important to understand.  There is a little more to it.  The way this set of principles is often written is "filter input, escape output."  A general design goes something like this:

1. Upon receiving input of any sort, make absolutely certain that it contains exactly what your programming expects.  For example, if it's an integer, test to see that it is an integer, or explicitly cast it to integer values.  If it's expected to be a positive integer, make sure it is >0.  Stuff like that.  In the case of text from a keyboard, you might use the PHP filters or some kind of regular expression to nullify the non-keyboard characters.

2. After you have sanitized your data, escape it with MySQLI_Real_Escape_String() or similar.  Prepare the query and Bind the variable to the query if you're using PDO.  Store it in the database as little changed as possible, subject to your rules of sanitization.  There are code examples in this article.

3. When you have run a SELECT query and recovered the data from the database and you're ready to prepare it for output (either directly with echo or in a template with HEREDOC notation), always run all textual data through htmlspecialchars() or similar.  This last step is important because you do not want to inadvertently broadcast evil JavaScript from your web site.
APD TorontoSoftware Developer


This actually needs to settle up to 50 invoices that the user identifies via checkbox.  

I forgot about using WHERE IN, which will make it possible for me to use binding.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial