Problems with mysql_real_escape_string

Question

I have field called filter1 on a form, I would like to be able to save quoted text into mysql. So I would like to be able to save the value "foo bar"...instead its saving just /

Here is what I have:

$keyword1 = mysql_real_escape_string($_POST['filter1']);

Any help is appreciated.

Here is how I construct the query

$keyword1 = mysql_real_escape_string($_POST['filter1']);
$keyword2 = $_POST['filter2'];//."|".$_POST['filterby'];
$keyword3 = $_POST['filter3'];//."|".$_POST['filterby2'];

$urlfilter1 = $_POST['url1'];
$urlfilter2 = $_POST['url2'];//."|".$_POST['url_filter'];
$urlfilter3 = $_POST['url3'];//."|".$_POST['url_filter2'];
//echo "combo_id:".$num." <BR></br>";
//echo "status:".$status." <BR></br>";
//echo "saveQuery:".$saveQuery." <BR></br>";
//$myFilter = "save"; 
$insert_query = sprintf("UPDATE COMBINATION 
                        SET STATUS_ID=%s, QUERY=\"%s\", 
                        KEYWORD1=\"%s\", KEYWORD2=\"%s\", KEYWORD3=\"%s\", 
                        URLFILTER1=\"%s\", URLFILTER2=\"%s\", URLFILTER3=\"%s\" 
                        WHERE COMBINATION_ID=%s",$status,$saveQuery,
                        $keyword1,$keyword2,$keyword3,
                        $urlfilter1,$urlfilter2,$urlfilter3,
                        $num);
//echo "insert_query:".$insert_query." <BR></br>";
$result = mysql_query($insert_query) or die(mysql_error());
if($result)
{
    echo "Saved successfully<br>";
}

} ?>

The problem is not `mysql_real_escape_string` but how you construct the SQL query. — Oswald, Apr 07 '11 at 15:59
...You do realize you're about 90% of the way to using prepared statements and solving your SQL problems altogether, right? A `new PDO` here, a `$db->prepare` there, a `s/(\\")?%s(\\")?/?/g` over there, and done. I swear, i don't get it... — cHao, Apr 07 '11 at 18:47

score 10 · Answer 1 · edited May 23 '17 at 09:58

10

Unless you have a very old and restricted environment, use PDO. It will save you buckets of sweat and tears. With PDO it is very easy to escape input and avoid SQL injection attacks, which is illustrated in the answer that this link leads to.

edited May 23 '17 at 09:58

Community

1
1

answered Apr 07 '11 at 16:01

Theo

131,503
21
160
205

1

There's a link to how to do it. I don't think repeating that answer serves any purpose. – Theo Apr 07 '11 at 16:37
+1 for being one of the few people to mention PDO around here. – cHao Apr 07 '11 at 18:49
There was a comment complaining about my answer not earning its keep, but it looks like it has been removed (which makes the second comment seem out of place). I've added another sentence to make it more clear that there is an example on how to use PDO if you follow the link. – Theo Apr 08 '11 at 04:54

score 1 · Answer 2 · answered Apr 07 '11 at 15:58

Well first you need to connect to the database with mysql_connect() http://php.net/manual/en/function.mysql-connect.php

Then you need to call your INSERT query with mysql_query() http://php.net/manual/en/function.mysql-query.php

By the way, you are doing the right thing by escaping the string before putting it into a query, well done :)

score 0 · Answer 3 · answered Apr 08 '11 at 05:08

0

For some reason you are escaping only one variable, while adding to the query several of them.
Why don't you escape them all?

However, your problem may be somewhere else. What is $saveQuery I am curious?

answered Apr 08 '11 at 05:08

Your Common Sense

156,878
40
214
345

Problems with mysql_real_escape_string

3 Answers3