Filtering User Input
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
You can build an array of forbidden values and use it in str_replace. Suppose you want get rid of ';', '<', '>':
You might be interested in learning about filter_input and sanitizing filters
$comment = $_POST['comment'];
$forbidden_values = array(';', '<', '>');
$result = str_replace($forbidden_values, '', $comment);
You might be interested in learning about filter_input and sanitizing filters
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(
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.
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(
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial