MYSQL escape string implementation

Question

I have a simple survey page that I was trying to make. Everything worked well except when I used a single quote in my comment on the survey page. When I had a comment with a single quote in it, the DB query wouldn't insert into the DB.

After some Googleing, I assumed I had to escape the string before inserting it into the DB. I used mysqli_real_escape_string to escape the string before INSERTing into the DB, but that doesn't seem to have helped.

Here is my code that inserts the user's comments into the DB ($con not shown for security)

    mysqli_real_escape_string($con,$_POST['question_1']);
    mysqli_real_escape_string($con,$_POST['question_2']);
    mysqli_real_escape_string($con,$_POST['question_3']);
    mysqli_real_escape_string($con,$_POST['question_4']);
    mysqli_real_escape_string($con,$_POST['question_5']);

    mysqli_query($con, "INSERT INTO feedback (question_1, question_2, question_3, question_4, question_5) VALUES ('$_POST[question_1]', '$_POST[question_2]', '$_POST[question_3]', '$_POST[question_4]', '$_POST[question_5]')");

Again, this only happens when the comment contains a single quote. Any suggestions? Did I escape the string incorrectly?

Assign a variable to it first `$q1=mysqli_real_escape_string($con,$_POST['question_1']);` then do `VALUES ('".$q1."')` etc.because what you're doing now `VALUES ('$_POST[question_1]',` is pointless. You can also use `stripslashes()` alongside that. Or, use [prepared statements](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and be done with it. No need to use additional functions. — Funk Forty Niner, Mar 19 '15 at 03:08
I would avoid `stripslashes()` just because it tends to miss some circumstances where things are not SQL safe. I've never had the escaping function fail to return SQL safe (not to be confused with security safe) strings — Machavity, Mar 19 '15 at 03:30
@Machavity Stripslashes allows to have O'Malley be written to DB exactly as shown, instead of O\'Malley. — Funk Forty Niner, Mar 19 '15 at 03:32

Machavity · Accepted Answer · 2015-03-19T03:28:59.507

You've misunderstood what is happening here

mysqli_real_escape_string($con,$_POST['question_1']);

This function returns an escaped string. You then use this string in your SQL

$question1 = mysqli_real_escape_string($con,$_POST['question_1']);
//Do your other escapes here
mysqli_query($con, "INSERT INTO feedback (question_1, question_2, question_3, question_4, question_5) 
    VALUES ('$question1', ...)");

A better solution here is prepared statements. This doesn't require you to escape anything. Data is sent separately and treated appropriately

$prep = mysqli_prepare($con, 'INSERT INTO feedback (question_1, question_2, question_3, question_4, question_5) 
    VALUES (?, ?, ?, ?, ?)');
mysqli_stmt_bind_param($prep, "sssss", $_POST['question_1'], $_POST['question_2'], $_POST['question_3'], $_POST['question_4'], $_POST['question_5']);
mysqli_stmt_execute($prep);

My previous understanding of the mysqli_real_escape_string was that it would update the variable that was given as a parameter. Thank you for correcting that. Everything works properly now but I will have to look into mysqli_prepare as that does sound like a much better way to accomplish the same task in fewer lines of code. — blazerunner44, Mar 19 '15 at 03:32

MYSQL escape string implementation

1 Answers1