Mysql insert doesn't work when the input field has an apostrophe

Question

For first time I have a mysql problem. I have an input field such as

<input type="text" name="myfield" id="myfield" />

So when the user presses the submit button I am getting the value with php

$myval = $_POST['myfield'];

Everything is ok so far. If my value in this input field contains an apostrophe ' and for example:

<input type="text" name="myfield" id="myfield" value="Niko's Dog" />

the mysql query:

mysql_query ("INSERT INTO users (myfield) VALUES ('$myval')");

Fails to insert the data..

Any opinions please? I need all characters to be valid.

Make use of `Prepared Statements` and you can stop worrying about this escaping and stuff. ! You are currently using `mysql_*` functions which is deprecated. — Shankar Narayana Damodaran, Nov 18 '13 at 11:44
`mysql_query ("INSERT INTO users (myfield) VALUES ('".mysql_real_escape_string($myval)."'");` — asprin, Nov 18 '13 at 11:45
just a suggestion, instead of `mysql_query` use `mysqli_query` as its more secure! — NoobEditor, Nov 18 '13 at 11:46

score 3 · Accepted Answer · answered Nov 18 '13 at 11:43

Try to add slashes like

$myval = addslashes($myval);
mysql_query ("INSERT INTO users (myfield) VALUES ('".$myval."')");

Either you can use mysql_real_escape_string directly.

mysql_query ("INSERT INTO users (myfield)
              VALUES ('".mysql_real_escape_string($myval)."')");

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

This happens when you don't escape your input

INSERT INTO users (myfield) 
VALUES ('Niko's Dog')
             ^-----------string end

You better use prepared statements. See here

edited May 23 '17 at 11:50

Community

1
1

answered Nov 18 '13 at 11:43

juergen d

201,996
37
293
362

score 0 · Answer 3 · answered Nov 18 '13 at 11:48

Look into using Prepared Statements, to avoid escape issues, as well as malicious input:

$result;
if ($stmt = $mysqli->prepare("INSERT INTO users (myfield) VALUES (?)") {
    $stmt->bind_param("s", $myval);
    $stmt->execute();
    $stmt->bind_result($result);
    $stmt->fetch();
    echo "Your result is $result";
    $stmt->close();
}

You ahould also be using mysqli_*, as the old mysql_* functions are deprecated.

score 0 · Answer 4 · answered Nov 18 '13 at 11:49

First of all, never ever trust user input! Always filter it before using, because user is evil (you have to think like that while writing any software).

Also, you should skip using deprecated mysql_* and instead use eg. PDO (or mysqli_* if you want to use something more similar to mysql_*) which is very easy and gives you more possibilities. Eg. allows you to use prepared statements. Using this allows you to write safer and better software.

score 0 · Answer 5 · answered Nov 18 '13 at 11:55

0

Just add below code into your server side file. so it will check every field of your form.

foreach ($_POST as $key => $value) {
    if(!is_array($value)){
        $_POST[$key] = mysql_real_escape_string($value);
    }
}

answered Nov 18 '13 at 11:55

Dhaval Bharadva

3,053
2
24
35

R R · Answer 6 · 2013-11-18T12:05:23.387

-1

other than addslashes and mysql_real_escape_string you can also use str_replace for the same:

$myval=str_replace("\'","'","$myval");

edited Nov 18 '13 at 12:05

answered Nov 18 '13 at 11:48

R R

2,999
2
24
42

Mysql insert doesn't work when the input field has an apostrophe

6 Answers6