I am working on a form with the possiblity for the user to use illegal/special characters in the string that is to be submitted to the database. I want to escape/negate these characters in the string and have been using htmlspecialchars(). However, is there is a better/faster method?
19
votes
There are 2 camps jumping on either 'illegal characters in a query' and 'illegal characters due to XSS attack / html'. I believe you're talking about the first, but you might want to clarify yourself a bit more.
– Wrikken
HTML is not SQL. Using HTML tools to avoid SQL problems is like using an English spell checker on Arabic text.
– Álvaro González
6 Answers
13
votes
If you submit this data to the database, please take a look at the escape functions for your database.
That is, for MySQL there is mysql_real_escape_string.
These escape functions take care of any characters that might be malicious, and you will still get your data in the same way you put it in there.
You can also use prepared statements to take care of the data:
$dbPreparedStatement = $db->prepare('INSERT INTO table (htmlcontent) VALUES (?)');
$dbPreparedStatement->execute(array($yourHtmlData));
Or a little more self explaining:
$dbPreparedStatement = $db->prepare('INSERT INTO table (htmlcontent) VALUES (:htmlcontent)');
$dbPreparedStatement->execute(array(':htmlcontent' => $yourHtmlData));
In case you want to save different types of data, use bindParam to define each type, that is, an integer can be defined by: $db->bindParam(':userId', $userId, PDO::PARAM_INT);. Example:
$dbPreparedStatement = $db->prepare('INSERT INTO table (postId, htmlcontent) VALUES (:postid, :htmlcontent)');
$dbPreparedStatement->bindParam(':postid', $userId, PDO::PARAM_INT);
$dbPreparedStatement->bindParam(':htmlcontent', $yourHtmlData, PDO::PARAM_STR);
$dbPreparedStatement->execute();
Where $db is your PHP data object (PDO). If you're not using one, you might learn more about it at PHP Data Objects.
30
votes
There are no "illegal" characters for the database. Database that cannot store some characters is a nonsense. There are some service characters, like quotes, used to delimit strings. These characters should be just escaped, not eliminated.
To send a query to the database, you have 2 options:
Build a query usual way, to make it look exactly as SQL query you can run in sql console.
To do it, one should understand a whole set of rules, not just "use mysql_real_escape_string".
Rules such as:- Strings should be both enclosed in quotes and escaped. That's the only meaning of escaping: It's just escape delimiters! (and some other characters - string termination char and escape character itself). Without surrounding quotes mysql_real_escape_string is just useless.
- Numbers should be cast to it's type explicitly. Though while data numbers can be threaten just like strings, there are some numbers, like LIMIT clause parameters, which cannot be escaped and can be only cast.
To send query and data separately.
This is most preferred way as it can be shortened to just "use binding". All strings, numbers and LIMIT parameters can be bound - no worry at all.
Using this method, your query with placeholders being sent to database as is, and bound data being sent in separate packets, so, it cannot interfere. It is just like code and data separation. You send your program (query itself) separated from the data.
But!
Everything said above covers only data part of the query.
But sometimes we have to make our query even more dynamic, adding operators or identifiers.
In this case every dynamic parameter should be hardcoded in our script and chosen from that set.
For example, to do dynamic ordering:
$orders = array("name","price","qty"); //field names
$key = array_search($_GET['sort'],$orders)); // see if we have such a name
$orderby = $orders[$key]; //if not, first one will be set automatically. smart enuf :)
$query = "SELECT * FROM `table` ORDER BY $orderby"; //value is safe
or dynamic search:
$w = array();
$where = '';
if (!empty($_GET['rooms'])) $w[]="rooms='".mesc($_GET['rooms'])."'";
if (!empty($_GET['space'])) $w[]="space='".mesc($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mesc($_GET['max_price'])."'";
if (count($w)) $where="WHERE ".implode(' AND ',$w);
$query="select * from table $where";
In this example we're adding to the query only data entered by user, not field names, which are all hardcoded in the script. For the binding the algorithm would be very similar.
And so on.
2
votes
First of all, you should sanitize things when displaying, not before inserting into the database. SQL injections are another story, but probably off-topic.
Second, if you don't need your users to be able to post HTML at all, htmlspecialchars is all you need. It takes care of all the special characters in HTML.
1
votes
I am working on a form with the possiblity for the user to use illegal/special characters in the string that is to be submitted to the database.
Users can go a lot beyond than that actually.
I want to escape/negate these characters in the string and have been using htmlspecialchars(). However, I would like to know if there is a better/faster method.
Use HTML Purifier:
HTML Purifier is a standards-compliant HTML filter library written in PHP. HTML Purifier will not only remove all malicious code (better known as XSS) with a thoroughly audited, secure yet permissive whitelist.
0
votes
This is not a problem you want to tackle on your own. There are libraries out there to do this for you, such as the HTML Purifier.
