|Special Character Handling|
Special characters in this context are characters that have a special meaning in the formal language they are used in. Usually it has a special meaning in the syntactical sense. Typically they include the ‘escape’ codes in that language.
For HTML, the special characters are single quote ', double quote ", the less than < and greater than > signs, and the ampersand & sign (HTML escape code).
For SQL it involves the string capabilities of the scripting language, as SQL-queries are denoted as a command in a string which contains quoted string parameters (i.e. quotes). In the examples here we use PHP for scripting.
For normal HTML texts, the HTML special codes (', ", <, >, &) are usually no problem, though it is wise the encode the characters <, > and & with their corresponding escaped values <, > and & (their so called Numerical Character Reference, NCR). HTML is rather forgiving for (syntactical) errors (it doesn’t display error messages, but tries to make the best of it). However, within HTML tags you must escape all the special characters in attribute data, in particular the quotes as they may otherwise be misinterpreted for syntax.
This occurs in particular for text <input> in HTML-forms, e.g.
<INPUT TYPE=TEXT NAME=F1 VALUE='It's late'>which is an HTML syntax error. HTML sees 'It' as string for the value to be presented to the user, doesn’t recognise (and ignores) “s late'” and continues after the >.
When coding HTML by hand, you can often obtain a desirable result by using the appropriate quotes (single or double) to enclose the attribute value.
However, when the value is unknown beforehand (e.g. a value from a database), you have a problem: the value may include ' and/or " (and/or any of the other special characters) which obviously interferes with the syntax.
So you always have to escape such use of (database) values in a form.
In PHP you can use the htmlspecialchars-function (or htmlentities) for this: it will encode the special characters (' " < > &) to their NCR-equivalent (usually the named variant ' " < > and & respectively). It has no consequence for the display of these characters, or for editing them (HTML considers an NCR sequence as a single character).
But this also means that you have to translate the HTML-entities back to their character code equivalent before entering the value in a database (or you will pollute the database with NCR sequences). This can be achieved through the html_entity_decode (or htmlspecialchars_decode)-function.
The last parameter in the htmlspecialchars- (and htmlentities-)function is for double encoding of HTML-entities; it should be false for our purposes (otherwise an entity like é is shown literally and not like é, usually an unwanted effect).
Htmlspecialchars and htmlentities are equivalent except that htmlentities will convert all characters which have an HTML-entity equivalent. For handling <input> values, htmlspecialchars conversion of ', ", <, > and & is adequate.
Similarly, html_entity_decode and htmlspecialchars_decode are equivalent except that html_entity_decode will decode all HTML-entities (not juist ' " < > and & but also e.g. é). It is usually desirable to convert all HTML-entities for a database (with potentially less desirable cases and ­ as they are not discernable anymore).
Example (in PHP, assuming HTML5 and character set UTF-8):
$val = ...; //e.g. "It's late" echo "<INPUT TYPE=TEXT NAME=F1 VALUE='" . htmlspecialchars( $val, ENT_QUOTES|ENT_HTML5, 'UTF-8', false ) . "'>";
In the script which processes the form containing this <input> you use:
$F1 = ... $_GET['F1'] / $_PUT['F1']; $F1 = stripslashes( $F1 ); $F1 = html_entity_decode( $F1, ENT_QUOTES|ENT_HTML5, 'UTF-8' );
Note that you need the stripslashes-function as the transfer of values through a form is also coded by addslashes. Or you can leave it out if you would use an addslashes immediately after that to insert it into an SQL-command (see next section).
But that’s all.
Above is also valid for the <textarea>-tag; not so much for the quotes as for the angular brackets. Make sure that you perform htmlspecialchars only once for each field, otherwise the consequences are the same as with double_encoding parameter true: you get the HTML-entities visibly in the input field.
If you want to display values containing HTML-tags (e.g. from a database) as text (show the tags and not let them be interpreted by the browser), you should use the htmlentities-function with double encoding true.
When handling SQL from a scripting language there is a basic problem in creating the query: SQL requires its commands as an ASCII string, but that query may contain parameters having quoted strings as values. E.g.
$name = "O'Neill"; $query = "UPDATE tbl3 SET name='$name' WHERE id=12345";which is interpreted by SQL as:
UPDATE tbl3 SET name='O'Neill' WHERE id=12345and leads to an SQL syntax error.
As single quotes ' are more common than double quotes ", one may occasionally circumvent the above problem by using double quotes as string separator in the query. But if a parameter value may (also) contain double quotes, it proves not to be a solution.
One may encode the quotes as ' or ' and " or " respectively: in HTML it will look the same, but it is not really the same in the database. So not a good solution either.
SQL allows an escape code in strings through the backslash \: the next character will be taken literally in the string. This is valid for strings enclosed by single quotes or by double quotes. And the same escape method applies to PHP !
The point is now to add the backslash just before each occurrence of a quote in the string. And before any occurrence of the backslash itself as it is the escape character (though it is a rare character). And PHP offers a simple function for that: addslashes.
$query = "UPDATE tbl3 SET name='" . addslashes( $name ) . "' WHERE id=12345";
Problem solved. And it prevents simple SQL-inserts at the same time.
But here as well, make sure you perform addslashes only once, otherwise you get backslashes in your database. And as you can read in the previous section, the transfer of values through a form is also coded by addslashes. So to be sure you may process the string first with stripslashes, or skip the addslashes if the value is straight from a $_GET or a $_PUT.
NB: There is also the function addcslashes (and stripcslashes) which has an extra parameter for the characters to quote.
That parameter may even include ranges of character, which is more powerful than we need.
In our case you would use '\'"\\'.
You may also use DBMS-specific escape functions (e.g. mysqli_real_escape_string()) but these are less easy to use.