When should you escape strings used in MySQL

Question

I'm fairly new to PHP but have been familiar with StackOverflow for a while.

I have recently been reading about appropriate times to use mysql_real_escape_string and would appreciate any advice on the following.

Is using mysql_real_escape_string once, on the initial $_POST variable enough to secure the string through the script?

For example:

$username = mysql_real_escape_string($_POST["username"]);
$password = mysql_real_escape_string($_POST["password"]);
$email = mysql_real_escape_string($_POST["email"]);
$repeat_password = mysql_real_escape_string($_POST["repeat_password"]);

I declare these values before running a bunch of if statements and finally once the if statements are finished I make an INSERT into the mysql database:

mysql_query("INSERT INTO users (username, password, email, signup_date) VALUES ('$username', '$password', '$email', CURDATE())") or die(mysql_error());

mysql_real_escape_string is not used anywhere else throughout the if statements - is this safe enough for a rookie to use whilst still maintaining some injection protection?

`mysql_real_escape_string` is used for escaping text strings — Dev Man, Apr 24 '14 at 23:42
hmm. I was under the impression that I had a decent idea of what it did - I guess I need to carry on my studies! — Daniel, Apr 24 '14 at 23:44
You shouldn't store the password. If you can't avoid it, salt it with a large random salt, hash it with a decent algorithm that doesn't have large rainbowtables available. — Chris Wesseling, Apr 25 '14 at 00:03
Very good that you are aware of injection.. But why doesn't the [canonical question](http://stackoverflow.com/q/60174/383793) answer your question? — Chris Wesseling, Apr 25 '14 at 00:06

score 1 · Answer 1 · answered Apr 24 '14 at 23:43

1

No, this is not safe. You should switch to prepared statements.

answered Apr 24 '14 at 23:43

CanSpice

34,814
10
72
86

1

While I agree that prepared statements are better, can you give an example of where his example could possibly fail? – mpen Apr 24 '14 at 23:45
1

Aside from the mysqli / pdo endorsement, @user3570991 should not store password in plain text. – dcclassics Apr 24 '14 at 23:52
2

@Mark: http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string – CanSpice Apr 24 '14 at 23:56
I'm aware of password encryption - I'm just trying to gain an understanding of the more in depth protection on this basic script before I continue to learn more about php – Daniel Apr 25 '14 at 00:00

score 1 · Answer 2 · edited May 25 '19 at 13:52

1

While mysql_real_escape_string() may (currently) protect you from SQL injection its deprecated so you should not you the mysql_* functions anyway, in future versions of PHP It will be removed rending your code useless.

Why drive a bashed up old ford fiesta when you have the keys to a shiny new Lamborghini?

Don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 13:52

Zoe

27,060
21
118
148

answered Apr 24 '14 at 23:54

Lawrence Cherone

46,049
7
62
106

NB: One needs to be very careful to use `mysql_real_escape_string()` correctly in order to benefit from the protection it offers. It's possible to be lulled into a false sense of security. See [SQL injection that gets around mysql_real_escape_string()](http://stackoverflow.com/q/5741187/623041). – eggyal Apr 25 '14 at 21:57

score 0 · Answer 3 · answered Apr 24 '14 at 23:45

0

Try a prepared statement:

$stmt = $con->prepare("INSERT INTO users (`username`, `password`, `email`, `signup_date`) VALUES (?, ?, ?, ?)");
$stmt->bind_param($username,$password,$email,CURDATE());
$stmt->execute();
$stmt->close();

answered Apr 24 '14 at 23:45

rmcfrazier

444
4
8

Note $con needs to be a string for connecting to your mysql server. – dcclassics Apr 24 '14 at 23:52

When should you escape strings used in MySQL

3 Answers3