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?
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();
$db is your PHP data object (PDO). If you're not using one, you might learn more about it at PHP Data Objects.
First of all, some people will say that simple-quoted strings are faster that double-quoted strings ; you should not care about that kind of micro-optimization : it will not make any difference for your application.
The difference between simple-quoted and double-quoted strings is :
- with double-quoted strings, there is variable interpolations
- with double-quoted strings, you can use some special characters like
- with single-quoted strings, you have simple-quotes to escape.
For reference, in the PHP manual :
- Single quoted
- Double quoted
I would that that, in the general matter, it's mostly a matter of personnal preferences...
Personnaly, in a situation such as the one you described, I would use a double-quoted string, like you did : it make the code easier to both write and read, as you don't have to escape that quote.
mysql_real_scape_string is for STRINGS. it will not make an integer 'safe' for use. e.g.
$safe = mysql_real_escape_string($_GET['page']);
will do NOTHING where
$_GET['page'] = "0 = 0";
because there's no SQL metacharacters in there. your query would end up something like
SELECT ... WHERE somefield = 0 = 0
However, doing intval() will convert that
0=0 into a plain
Any query can be injected whether it's read or write, persistent or transient. Injections can be performed by ending one query and running a separate one (possible with
mysqli), which renders the intended query irrelevant.
Any input to a query from an external source whether it is from users or even internal should be considered an argument to the query, and a parameter in the context of the query. Any parameter in a query needs to be parameterized. This leads to a properly parameterized query that you can create a prepared statement from and execute with arguments. For example:
SELECT col1 FROM t1 WHERE col2 = ?
? is a placeholder for a parameter. Using
mysqli, you can create a prepared statement using
prepare, bind a variable (argument) to a parameter using
bind_param, and run the query with
execute. You don't have to sanitize the argument at all (in fact it's detrimental to do so).
mysqli does that for you. The full process would be:
$stmt = $mysqli->prepare("SELECT col1 FROM t1 WHERE col2 = ?"); $stmt->bind_param("s", $col2_arg); $stmt->execute();
There is also an important distinction between parameterized query and prepared statement. This statement, while prepared, is not parameterized and is thus vulnerable to injection:
$stmt = $mysqli->prepare("INSERT INTO t1 VALUES ($_POST[user_input])");
- All Queries should be properly parameterized (unless they have no parameters)
- All arguments to a query should be treated as hostile as possible no matter their source
- ditch mysqli in favor of PDO (with mysql driver)
- use PDO paremeterized prepared statements
You can then do something like:
$pdo_obj = new PDO( 'mysql:server=localhost; dbname=mydatabase', $dbusername, $dbpassword ); $sql = 'SELECT column FROM table WHERE condition=:condition'; $params = array( ':condition' => 1 ); $statement = $pdo_obj->prepare( $sql, array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY ) ); $statement->execute( $params ); $result = $statement->fetchAll( PDO::FETCH_ASSOC );
- No more manual escaping since PDO does it all for you!
- It's relatively easy to switch database backends all of a sudden.
- i cannot think of any.