Asked  7 Months ago    Answers:  5   Viewed   32 times

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?

 Answers

77

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.

Wednesday, March 31, 2021
 
saad
answered 7 Months ago
34

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 n, t, ...
  • 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.

Wednesday, March 31, 2021
 
Kenny
answered 7 Months ago
82

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 0.

Saturday, May 29, 2021
 
e_i_pi
answered 5 Months ago
80

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])");

To summarize:

  • 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
Monday, June 7, 2021
 
e_i_pi
answered 5 Months ago
36

My recommendations:

  1. ditch mysqli in favor of PDO (with mysql driver)
  2. 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 );

PROs:

  1. No more manual escaping since PDO does it all for you!
  2. It's relatively easy to switch database backends all of a sudden.

CONs:

  • i cannot think of any.
Monday, August 2, 2021
 
the12
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :