CODEWITHSUNDEEP
phpmysql
Reham Fahmy
Reham Fahmy

Reputation: 5063

Error when update my database

If I have the following:

$common = Amelia's sand verbena

When I try to do the following update:

$clear=strip_tags($common);
$clear=preg_replace('/[^\w ]/', '', $clear);
$clear=str_replace('  ', '-', $clear);
$clear=str_replace(' ', '-', $clear);
$clear= strtolower($clear);


$sql= "update my_table set sid='$clear' WHERE common= '$common'";
mysql_query($sql) or die("query failed: $sql".mysql_error());

I get the following error:

query failed: update my_table set sid='amelias-sand-verbena' WHERE common= 'Amelia's sand verbena'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's sand verbena'' at line 1

Why am I getting this error?

Upvotes: 1

Views: 73

Answers (4)

Jory Geerts
Jory Geerts

Reputation: 1977

$safe_common = mysql_real_escape_string($common);
$safe_clear = mysql_real_escape_string($clear);
$sql= "update my_table set sid='$clear' WHERE common= '$save_common'";

That does what you need. Now please go read https://www.php.net/mysql%20real%20escape%20string and https://www.php.net/manual/en/security.database.sql-injection.php . :)

Upvotes: 0

Yaniro
Yaniro

Reputation: 1587

The single quote character is part of the SQL syntax. If you have it in one of your strings you have to escape it with another single quote. If your string is: 'abc's lalala' it must become 'abc''s lalala', this is a very common issue which can lead to SQL injection problems so use mysql_real_escape_string() on your strings, check it out at http://www.php.net/manual/en/function.mysql-real-escape-string.php

Upvotes: 1

Prasad Rajapaksha
Prasad Rajapaksha

Reputation: 6190

Try this code.

$clear=strip_tags($common);
$clear=preg_replace('/[^\w ]/', '', $clear);
$clear=str_replace('  ', '-', $clear);
$clear=str_replace(' ', '-', $clear);
$clear= strtolower($clear);
$common=str_replace("'", "\'", $common);


$sql= "update my_table set sid='$clear' WHERE common= '$common'";
mysql_query($sql) or die("query failed: $sql".mysql_error());

Upvotes: 2

nand
nand

Reputation: 627

Firstly, you don't strp or escape quotes. Secondly, you're missing a trailing single quote in your query.

$sql= "update my_table set sid='$clear' WHERE common= '$common'";

Upvotes: 0

Related Questions