String problems when trying to insert to SQL!

[greens85]

Hi all,

 

I'm having a hard time with strings...

 

I am pulling data from an XML feed, and then trying to insert into my database. All the code is wrote, the problem is that some of the descriptions contain both ' and " so it is messing with my insert statement.

 

I can get over the ' by doing this: \"$newstring\" however it then fails on " 

 

I have tried every function I can think of:

 

str_replace, mysql_real_escape_string, htmlspecialchars, addslashes, stripslashes etc. etc. nothing seems to do the trick!

 

I have attached the code in full, would someone be able to take a look and help me out?

 

Any help would be greatly appreciated!

 

many thanks

 

Greens85

 

[attachment deleted by admin]

[PFMaBiSmAd]

It would be helpful if you echoed $query for a value that is failing and post the actual query along with the actual msyql_error() message that you get for that query.

[greens85]

Hi,

 

Thanks for your prompt reply!

 

The query is long... as it is inserting all until it encounters a ' or ";

 

So I am just going to post the SQL error:

 

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 experience teaching. The pupils are in KS3 and KS4 (up to GCSE) and have Emot' at line 3

 

If you want to see the result of the echo you can do so here:

 

http://www.beta.educationvacancies.com/AXCIS_xml_feed.php

 

Many thanks

 

Greens85

[BlueSkyIS]

How I write an SQL statement with $a_string containing ' and/or ":

 

$a_string = mysql_real_escape_string($a_string);
$sql = "INSERT INTO some_table VALUES ('$a_string')";
mysql_query($sql) or die(mysql_error() . " IN $sql");

 

Note: I double-quote the entire SQL and single-quote ALL values (numeric and text). Using mysql_real_escape_string ensures that the value will work in single-quotes.

[greens85]

Hi BlueSkyIS,

 

Many thanks for your response! Sorry I have misunderstood this but isn't that what I'm doing anyway just in a different manner:

 

// Variable is in mysql_real_escape_string
$newstring = mysql_real_escape_string($item->description); 

// Entire query is wrapped in double quotes and value variables are wrapped in single quotes
$query = 
	"INSERT INTO jobs 
	(jobid, jobref, employerid, position, category, country, subcounty, contract, hour, salary, description, start, postdate, cname, tel)
	VALUES ('$jobid', '$reference', '117', '$title', '$category', '$country', '$location', '$jobtype', '$hours', '$salary', '$newstring',
	'$start', '$EXmonth/$EXday/$EXyear', '$contactname', '$contacttel')";
	$result = mysql_query($query) or die (mysql_error());

 

I'm not great as PHP (hence my post) so if I have misunderstood I am sorry.

[BlueSkyIS]

On the sample page, the error at the bottom does not correspond to the SQL above. Can you update the mysql_error() to show the actual SQL that is being executed?

 

mysql_query($sql) or die(mysql_error(). " IN THIS SQL: $sql");

[greens85]

I'm not sure why...

 

I have 100% made no changes to the code since I uploaded and ran that test!

 

Could the error be coming from something else 

 

[EDIT] Sorry misunderstood you post... will make the changes now!

[BlueSkyIS]

mysql_query($somesqlhere) or die(mysql_error(). " IN THIS SQL: $somesqlhere");

[BlueSkyIS]

It appears that the value for description has not been mysql_real_escape_string'ed

[greens85]

I trust I have now understood correctly:

 

My code of this:

 

$query = 
	"INSERT INTO jobs 
	(jobid, jobref, employerid, position, category, country, subcounty, contract, hour, salary, description, start, postdate, cname, tel)
	VALUES ('$jobid', '$reference', '117', '$title', '$category', '$country', '$location', '$jobtype', '$hours', '$salary', '$newstring',
	'$start', '$EXmonth/$EXday/$EXyear', '$contactname', '$contacttel')";

	mysql_query($query) or die(mysql_error(). " IN THIS SQL: $query");
echo $query;

 

Produced:

 

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 experience teaching. The pupils are in KS3 and KS4 (up to GCSE) and have Emot' at line 3 IN THIS SQL: INSERT INTO jobs (jobid, jobref, employerid, position, category, country, subcounty, contract, hour, salary, description, start, postdate, cname, tel) VALUES ('428', 'Axcis/031213', '117', 'Part time SEN Teacher', 'Special Needs Teacher Jobs', 'Eastern', 'Essex', 'Temporary', 'Part Time', 'Negotiable', 'A Pupil Referral Unit in West London requires a full-time Maths & ICT teacher to start after the October half-term. You must have a formal teaching qualification, and at least one full year's experience teaching. The pupils are in KS3 and KS4 (up to GCSE) and have Emotional and Behavioural Difficulties (EBD). You don't necessarily need to have previous experience working with EBD pupils, however you must have the right kind of personality - a caring and flexible nature. If you think you fit the bill, please do not hesitate to apply. You can do so through the Axcis website, or by emailing your up-to-date CV to catherine@axcis.co.uk Pay will be in the region of ?145 per day, and only successful candidates will be contacted. ', '23 Sep 2010', '10/23/2010', 'Mari-Lize Aucamp', '@ Axcis 02075802956')

[PFMaBiSmAd]

The error is because your logic for $count is wrong.

 

You only use mysql_real_escape_string() on the description when if($count > 0){

 

If you look at your queries, you will see that the first three queries are correct, but after that they reuse everything but the description because the if($count > 0){ logic causes the code that is setting all the other values and using mysql_real_escape_string() on the description is being skipped over.

[greens85]

Hi PFMaBiSmAd,

 

I think I get what you are saying:

 

Because the escaping is done inside the if... the description will only get escaped so many times?

 

Would the solution be using a while statement instead... as I said before I'm not great at PHP so hopefully I'm at least along the right lines!

 

Thanks

[PFMaBiSmAd]

Based on the comments in the code, $count is being used to limit the number of items to display/process. If that is so, the query logic should be inside of the if($count > 0){ ... } logic, not outside of it.

[greens85]

As you may have guessed i didnt 100% write this so im not sure! I will try and take your comments to fix this, if not I shall post back once I've had a go!

 

Many thanks for everyones responses and help.

 

[EDIT] Looking at it again, would changing the count to something higher than 3 help... let say I changed it to 1000?

[PFMaBiSmAd]

If you always want to process all the items, you should just remove all the lines of code related to $count, don't forget the closing } that matches the if($count > 0){ line.

[greens85]

Hi PFMaBiSmAd,

 

Sorry for the delayed response!

 

I would ideally like to only process all of the items the first time it is run i.e. now... then each additional time the script runs. This will be automated on a CRON job, I only want to add items that aren't already in the database.

 

I think my code will already achieve this, but obviously the section of code you mentioned in your previous post is failing in someway.

 

Is is possible that rather that I could;

 

remove the code

run the script

get all the items in the database

 

put the code back in and set it on a cron, then it would add only additional items?

 

many thanks,

 

greens85

[PFMaBiSmAd]

The logic using $count would not do anything for detecting what is or is not already in the database.

[greens85]

So now I have happily imported all records, I can safely remove that logic?

 

This is my check for existing records:

 

$query = "SELECT * FROM jobs WHERE jobref ='$reference'";
$result = mysql_query($query) or die (mysql_error());
	if(mysql_num_rows($result)==0){

        // increment the jobid based on the last jobid in the database
	$qc = "SELECT jobid FROM jobs ORDER BY jobid DESC";
		$rc = mysql_query($qc) or die (mysql_error());
			$ac = mysql_fetch_array($rc);
				$jobid = $ac[0] + 1;
}