3

So basically I’ve been digging deep into the realm of MySQL and PHP…specifically the security measures I should take when dealing with a database and form inputs. So far I’ve found that the following are very strongly recommended:

  1. Prepared Statements
  2. Using mysqli_real_escape_string()
  3. NOT using Magic Quotes as it confuses databases and ends up giving you stuff like “You\’re name isn\’t….”

All of this is great and I’ve been following it. However, I was wondering if one should also escape characters such as the dollars sign [$], percentage sign [%], and possibly others. Couldn’t the query interpret the dollar sign as a PHP variable perhaps? What about LIKE syntax I’ve heard that uses the % symbol or even the wildcard sign? Prepared statements should technically take care of all of this, but I just wanted to be safe and make sure I had everything escaped properly. In the case that I forget to use prepared statements or just neglect to do them, I was hoping this second line of defense per-say could save me a loooong headache.

Here is what I use for escaping currently:

function escape($connection, $data){
    $new_data = trim($data);
    $new_data = mysqli_real_escape_string($connection, $new_data);
    $new_data = addcslashes($new_data, '%_$');
    $new_data = htmlspecialchars($new_data, ENT_NOQUOTES);

    return $new_data;

}

So is this proper? Am I doing something horrendously wrong? Notice that I would have to remove back slashes before the $, %, and _ characters when retreiving the database data.

Improve this question

3 Answers 3

Reset to default
4

You don't need to escape dollar sign. MySQL doesn't treat that character specially, and PHP only recognizes it in source code, not in string values (unless you call eval on the string, but that's a whole other can of worms).

You would only need to escape % and _ if you used user input as the argument to LIKE and you didn't want the user to be able to use wildcards. This could come up if you're processing a search form. You don't need to use it when storing into the database.

You don't need to use htmlspecialchars when accessing the database. That should only be used when you're displaying data to the user in an HTML page, to prevent XSS injection.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Am I doing something horrendously wrong?

Yes.

First on your research.

Prepared Statements is the only great thing you have found.

While use of mysqli_real_escape_string (assuming you are using prepared statements) would be useless and harmful (producing the outcome you have noted yourself: “You\’re name isn\’t….”).

And Magic Quotes has been removed from the language long time ago already - thus, nothing to concern actually.

So, even most of your initial premises are plainly wrong.

Now to your question.

Couldn’t the query interpret the dollar sign as a PHP variable perhaps?

No.

What about LIKE syntax I’ve heard that uses the % symbol or even the wildcard sign?

Yes, you've heard it right. That's exact purpose of LIKE operator - to perform a wildcard search. Disabling these symbols in LIKE would make not a slightest sense.

Means every time you are going to use LIKE operator, you have to decide which particular symbol to use and which to disallow. NO one-for-all solution can be used. Not to mention that in all other mysql interactions % sign has no special meaning at all.

Prepared statements should technically take care of all of this

Prepared statements has nothing to do neither with $ nor with % signs. Prepared statements deal with SQL injections, but neither symbol could cause it (wouldn't you call "injection" a proper intended use of LIKE operator, would you?).

Finally, to the most horrendous part.

In the case you forget to use prepared statements or just neglect to do them,

nothing can save you.

And least help would be from the function you developed.

To sum it all up.

  1. Get rid of this function.
  2. Use placeholders* to represent every single variable in the query.
  3. Escape % and _ symbols in the input data only if it's going to be used in LIKE operator and you don't want them to be interpreted.
  4. Use htmlspecialchars() for output, not mysql input.

*read on prepared statements if the term is unfamiliar to you.

Improve this answer

2 Comments

Great stuff! I'll make sure to always use prepared statements from now on. It seems to be that this answer pointed out a couple things I did not know, and in a very clear manner. Thanks again!
For those who want to flag this, the answerer is using the same language the questioner used. The questioner is not offended so neither should you be. Your flags will be declined.
-1

Depending on what kind of data and what it is used for.

If you find PHP default prepared statements are too long and complex to remember I suggest to have look at some classes available on github to give you an idea of simplified queries.

A Good example @ https://github.com/joshcam/PHP-MySQLi-Database-Class

An example of insert queries with this class

$data = Array (
    'login' => 'admin',
    'active' => true,
    'firstName' => 'John',
    'lastName' => 'Doe',
    'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
    // password = SHA1('secretpassword+salt')
    'createdAt' => $db->now(),
    // createdAt = NOW()
    'expires' => $db->now('+1Y')
    // expires = NOW() + interval 1 year
    // Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
);

$id = $db->insert ('users', $data);
if ($id)
    echo 'user was created. Id=' . $id;
else
    echo 'insert failed: ' . $db->getLastError();
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.