SQL injection - protection

Question

I am not using prepared statements and i dont want to use it if not necessary

Example :

$sqlServ = new mysqli(SQL_HOST, SQL_USER, SQL_PASS);
$char = isset($_GET['char']) ? sanitize($_GET['char']) : null;
$check = $sqlServ->query("SELECT name FROM player.player WHERE name LIKE '{$char}'");

function sanitize($var)
{
    $var = htmlentities($var, ENT_QUOTES);
    $var = htmlspecialchars($var, ENT_QUOTES, 'UTF-8');
    if (get_magic_quotes_gpc()) {
        $var = stripslashes($var);
    }
    return $var;
}

Is it enough for sql injection protection? Thanks for answer.

***NO!*** the only reliable way to protect your code from SQL injection is to use ***parametrized queries*** and not concatenate together your SQL statements! Stop wasting time on trying to "filter" or "sanitize" your SQL - just **use parameters** and be done with it! — marc_s, Jul 09 '15 at 10:17
Don't be lazy, listen to marc_s' advise, parameter markers is the way to go. — jarlh, Jul 09 '15 at 10:22
Good explanation here : http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — patricmj, Jul 09 '15 at 10:28

score 1 · Answer 1 · answered Jul 09 '15 at 16:40

$var = htmlentities($var, ENT_QUOTES);

This is a bad idea. HTML-encoding is for the output stage, where you <?php echo htmlspecialchars($var); ?> variables in an HTML template. If you try to handle this output-time concern at input-time you end up with input that is unnecessarily encoded in the database, preventing you from properly matching, slicing and sorting it. And if you then output the content from the database without escaping, you've still got XSS bugs for any content that comes in through a means other than form input.

HTML-encode when creating HTML and at no other time.

$var = htmlspecialchars($var, ENT_QUOTES, 'UTF-8');

Huh? It's already HTML-encoded, why double-encode it? This is guaranteed to mangle characters like & and <, as well as (because of the previous htmlentities) all non-ASCII characters.

Is it enough for sql injection protection?

Not exactly. Mainly: there is no SQL injection protection code here at all.

There is HTML-escaping, but backslashes are special characters in MySQL string literals, and backslashes are not touched by HTML-escaping. So input strings with a trailing backslash break the end ' in the query, making the query invalid.

It so happens that because there are no other ' characters or injections in the string after that, there isn't a way to exploit this particular query; you can only make it break. But as soon as the query changes that's no longer true.

To do SQL injection ‘protection’, you would call $sqlServ->real_escape_string() on the value just before putting it into the string literal in the query. But this is really easy to forget or get wrong. Using parameterised queries consistently is a more reliable approach and no more difficult.

SQL injection - protection

1 Answers1