Insert query with special character in php

Question

Here is my code

<?php

$con    =   mysql_connect('localhost','test','test');
mysql_select_db('test',$con);


require_once("xml2json.php");

$testXmlFile = 'mytest.xml';

$xmlStringContents = file_get_contents($testXmlFile); 
$jsonContents = "";
$jsonContents = xml2json::transformXmlStringToJson($xmlStringContents);
$obj =json_decode($jsonContents);
$rows = array();
foreach($obj->rss->channel->item as $item) {

     echo $item->title."\n";
     echo $item->description."\n";
     $rows[] = "('".mysql_real_escape_string($item->title)."','".mysql_real_escape_string($item->description)."')";
} 
        $del_horoscope = "delete from jos_horoscope";
        mysql_query($del_horoscope);

        $query = mysql_real_escape_string("INSERT INTO `jos_horoscope` (`title`,`description`) VALUES ".implode(', ',$rows));
        mysql_query($query);

  if (!mysql_query($query)) echo 'Oh no! Something went wrong with the query: '.mysql_error();

?>

I am not able to insert the title and description in DB. It always says

Oh no! Something went wrong with the query: 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 '\'What\'s special today for you\',\'Your birth on the 25th day of the month (7 e' at line 1

Some of the Data which I am trying to insert is

What's special today for you
Your birth on the 25th day of the month (7 energy) modifies 
your life path by giving you some special interest in technical,
 scientific, or other complex and often hard to understand subjects.
 You may become something of a perfectionist and a stickler for
 details. Your thinking is logical and intuitive, rational and responsible.
 Your feelings may run deep, but you are not very likely to let them
 show. This birthday makes you a more private person, more 
introspective and perhaps more inflexible.
Aries Horoscope for Thursday, April 25, 2013
Although most of us when we make a decision we don`t have to worry what it will impact for the world we live in, but when you make a decision we have to remember that it does impact our society in so many ways. Our choice has to be an ethical one, regardless of the outcome. Your emotions are brought into more conscious focus these few days. You may find that you can more easily communicate your feelings at this time.

The First line is title and rest is Description till Aries Horoscope . After that another title starts . Please help me . I tried many options but its not working for me

Here is my table structure

Please help me .

it's 2013 and people still haven't taken their time to read about XSS and SQL injection... — pocesar, Apr 25 '13 at 17:34
@pocesar Please guide me ... what improvements I do in my code .. — Yogus, Apr 25 '13 at 17:37
@user2320808 mysql_* functions are depricated, [you can use mysqli_* instead](http://idiallo.com/blog/2013/04/dealing-with-mysql-once-and-for-all.html). Not that it solves your problem but it will be a good start to properly right your query in php — Ibu, Apr 25 '13 at 17:48
Please take a look at http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php which will also solve your problem if you decide to switch to prepared statements. — , Apr 25 '13 at 17:58
@Ibu my server is not supporting the mysqli. Thats why I am using mysql. The improvemnet code given by you below is still giving me same problem .. Please suggest new one. — Yogus, Apr 25 '13 at 23:55

score 3 · Accepted Answer · answered Apr 25 '13 at 17:51

3

You are escaping your query multiple times, here is the correct way:

foreach($obj->rss->channel->item as $item) {
     $rows[] = "('".mysql_real_escape_string($item->title)."','".mysql_real_escape_string($item->description)."')";
}

Now you don't need to escape it again:

$query = "INSERT INTO `jos_horoscope` (`title`,`description`) VALUES ".implode(', ',$rows);
mysql_query($query);

answered Apr 25 '13 at 17:51

Ibu

42,752
13
76
103

from all the answer, this is the right one, regardless of using deprecated functions. – pocesar Apr 25 '13 at 18:21
@user2320808 from the error you are showing, it still looks like you are escaping multiple times. Do you have magic_quotes on? try to disable it – Ibu Apr 26 '13 at 00:15
Beautiful . Sorry I forgot to remove escape from insert query . Now it worked !! thanks :D – Yogus Apr 26 '13 at 00:20

score 0 · Answer 2 · answered Apr 25 '13 at 17:55

0

First of all, you don't want to escape the actual INSERT string. Just the values you are going to INSERT.

$query = mysql_real_escape_string("INSERT INTO `jos_horoscope` (`title`,`description`) VALUES ".implode(', ',$rows));

Is what you are currently doing. Just escape each of the values you are going to insert. Which leads us to the next problem. Your $rows[] array you are building inside the foreach loop isn't formatted properly if the foreach runs multiple times. Your insert should be inside of the foreach. And just scrap the array.

foreach($obj->rss->channel->item as $item) {
 echo $item->title."\n";
 echo $item->description."\n";
 $title = mysql_real_escape_string($item->title);
 $description = mysql_real_escape_string($item->description);

$query = "INSERT INTO `jos_horoscope` (`title`,`description`) VALUES ('$title', '$description')";
mysql_query($query);
}

Then I'm not really sure what the delete query is for.

answered Apr 25 '13 at 17:55

Cory Shaw

1,130
10
16

3

Regarding "[...] isn't formatted properly if the foreach runs multiple times": MySQL does allow inserting multiple rows using a single query. – PleaseStand Apr 25 '13 at 17:57
@mbsurfer I tried wit your code it works but only last title and description gets inserted not all. Please help why my all values are not inserted . – Yogus Apr 25 '13 at 23:50
@PleaseStand Yeah I am only able to insert last title and description . Where could I change my code to make it multiple entries. – Yogus Apr 25 '13 at 23:57
`$query = "INSERT INTO \`jos_horoscope\` (\`title\`,\`description\`,\`value1\`,\`value2\`,\`etc\`) VALUES ('$title','$description','$value1','$value2','$etc')";` – Cory Shaw Apr 26 '13 at 16:39

Insert query with special character in php

2 Answers2