mysql query does not get updated due to apostrophe sign

Question

$url = "example.com";
$data = json_decode($raw);
    $pname=$data->name;
$sql="UPDATE `client` SET pname='$pname' WHERE url='$url'";
    $query=mysql_query($sql,$link)or die(mysql_error());

When the json data is decoded, the value in variable $pname goes in client table. If there is an apostrophe sign (') in name then it throws an error. What changes can I make in the variable to send the name to database table?

example: Jerry get updated with no issues D'Cunha does not get updated as it has the apostrophe sign. The query becomes

"UPDATE `client` SET pname='D'Cunha' WHERE url='example.com'"

I found some articles but that does not say about how to find the apostrophe sign and change the variable value

`stripslashes()` and `mysql_real_escape_string()` will fix that. — Funk Forty Niner, Nov 04 '14 at 03:55
This can solve if I want to run a single query. I am looping hundreds of data. How can the variable $pname value be changed with an if statement? if there is a an apostrophe sign, replace it with the format you suggested... — Mumbai CabinCrew, Nov 04 '14 at 03:57
Inject `mysql_real_escape_string()` in the query itself then, or use prepared statements, as already stated. — Funk Forty Niner, Nov 04 '14 at 03:58
how to use/inject mysql_escape_sring() stripslashes() and mysql_real_escape_string()? sorry dont know what do you mean by that. can the sign be identified with a if statement and change the value of the variable? — Mumbai CabinCrew, Nov 04 '14 at 04:01

score 3 · Accepted Answer · answered Nov 04 '14 at 04:00

3

use mysql_escape_string()

$sql="UPDATE `client` SET pname='".mysql_escape_string($pname)."' WHERE url='$url'";

and learn mysqli or PDO as mysql is deprciated and soon going to be drop

answered Nov 04 '14 at 04:00

arif_suhail_123

2,509
2
12
16

This will certainly work but it should be noted that PHP specifically [warns against this](http://php.net/manual/en/function.mysql-escape-string.php): ***Warning** This function has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.* – Mark Miller Nov 04 '14 at 04:10
Ah, another happy ending ;) – Funk Forty Niner Nov 04 '14 at 04:48

score 2 · Answer 2 · edited May 23 '17 at 12:20

2

Use prepared statements. Mysqli or PDO. Here's an example with mysqli:

$url = "example.com";
$data = json_decode($raw);
$pname=$data->name;

$mysqli = new mysqli($host, $user, $password, $db);

$stmt = $mysqli->prepare("UPDATE client SET pname = ? WHERE url = ?");
$stmt->bind_param("ss", $pname, $url);
$stmt->execute();

Why shouldn't I use mysql_* functions in PHP?

edited May 23 '17 at 12:20

Community

1
1

answered Nov 04 '14 at 04:05

Mark Miller

7,442
2
16
22

1

Thanks Mark... Im a beginner and will definitely read about prepared statements – Mumbai CabinCrew Nov 04 '14 at 04:09

score 1 · Answer 3 · answered Nov 04 '14 at 03:56

1

Try this:

UPDATE client SET pname = 'D\'Cunha' WHERE url = 'example.com'

answered Nov 04 '14 at 03:56

FortMauris

167
7

mysql query does not get updated due to apostrophe sign

3 Answers3