Quote in data item causes syntax error on synch

[royvarley]

Following on from Peter's thread:

When a data item contains ' I get an error in the log file:

Error No. 1064

You have an error in your SQL syntax near 's Quarry', `CostumeDescription`=NULL, `CompanyID`='1', `ProjectID`='15', `Sectio' at line 1

The data in the field was Mr Slate's Quarry

The record containing this field does not update.

SJA V4.1 from SQLYog 4.07 download (Windows)

[Ritesh]

Can you mail me sample data to reproduce the problem?

[royvarley]

I was trying to replicate this error so that I could send a sample db.

If I set up the two databases with just a single table in each on my windows machine, the synch works. No problem.

If I set up the destination db on the website (just the same, single table) and then try to synch, I get the error as described.

This was running the synch job immediately from SQLYog.

Something is different about running the synch locally and running it across to a destination website. The destination website is Linux. MySQL version is the same on both sites.

I assume that SQLYog/SJA on my local machine generates the command files for MySQL on the destination machine? It is either generating something different or it's different when it gets to the destination to execute.

[Ritesh]

What are your MySQL versions?

We have Linux boxes out here. If you can just send me a dump of your db, I will try to replicate the same enviroment at my end.

[royvarley]

FYI: I tried it with 4.1 beta 7, just in case. Problem is still there.

My gut feel tells me it's the parser in SQLyogTunnel.php – not handling an escaped single quote inside another pair of quotes… or something like that.

[peterlaursen]

Are you sure that your php tunnelling file at the ISP is latest version ?

[peterlaursen]

and one Q more:

what is the PHP version runing at your ISP ?

I myself have an issue with sync and php5 (its an issue with the character).

If that is it I have a setup of various MySQL's <> php5 <> apache running here. So if you'll let me have some sample data I can see if I can reproduce.

SJA-sync is written with php4 in mind and uses the php_mysql extension.

I know that it is on the TODO to support the php_mysqli extension too and to support the new “database abstraction layer” in php introduced with php5.1.

But other task/issues/features have been considered more important till now.

However as we can expect php5.1 final i about a moth's time, I think it should soon be ..

[Ritesh]

This is very stange. I received the dump sent by you. With the latest SQLyogTunnel.php (bundled with v4.07 and v4.1 binaries), I am able to correctly sync the two databases.

Test #1

Source MySQL: 3.23.58-max-nt (direct connection)

Target MySQL: 3.23.58-max-nt (HTTP Tunneling)

Test #2

Source MySQL: 3.23.58-max-nt (direct connection)

Target MySQL: 3.23.58 (HTTP Tunneling, our ISP)

Test #3

Source MySQL: 3.23.58-max-nt (HTTP Tunneling)

Target MySQL: 3.23.58-max-nt (HTTP Tunneling)

Test #4

Source MySQL: 3.23.58 (HTTP Tunneling, our ISP)

Target MySQL: 3.23.58-max-nt (HTTP Tunneling)

Are you sure that you have uploaded the latest SQLyogTunnel.php to your webserver?

[Ritesh]

I am always getting the following result:

Code:

SQLyog Job Agent Version 4.1
Copyright (c) Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Tue Aug 02 16:43:33 2005

Table                      SrcRows  TgtRows  Inserted  Updated  Deleted
=========================  =======  =======  ========  =======  =======
`roles`                        186      185         1        0        0  

[royvarley]

Well it's the one that's in the SQLyog directory. It has a creation date/time of 1-Feb-2005 3:26:05PM.

My ISP is still running PHP 4.3.8

[peterlaursen]

Quote:

Well it's the one that's in the SQLyog directory

well.. what i meant was that if it was an early version originating form an early SQLyog 4.0 Beta, that could maybe be the reason. If your are not perfectly sure I'd propose that you upload the file again.

[royvarley]

I upgraded to 4.1 beta 7 today. The file looked the same but I uploaded it to the website anyway.

[peterlaursen]

and problem persists ??

[royvarley]

Yes.

I've been running the tunnel in debug mode with a few extra writelogs.

The raw XML, passed to the tunnel, that generates the query is:-

insert into `roles` values ('183', 'Mr Slate's Quarry', NULL, '1', '15', '43')

Then we execute:

$xmlrecvd = stripslashes ( urldecode ( $_POST['textfield'] ) );

which gives a value of $xmlrecvd of:

insert into `roles` values ('183', 'Mr Slate's Quarry', NULL, '1', '15', '43')

We lose the

This is then translated to:

insert into `roles` values ('183', 'Mr Slate's Quarry', NULL, '1', '15', '43')

which is wrong.

[peterlaursen]

Quote:

This is then translated to:

insert into `roles` values ('183', 'Mr Slate's Quarry', NULL, '1', '15', '43')

which is wrong.

because it should be

Quote:

insert into `roles` values ('183', 'Mr Slate's Quarry', NULL, '1', '15', '43')

Agreed ?

My belief is that it be a problem with repective php & mysql versions and configuration!

[peterlaursen]

Now I understand in detail!

The stripslashes() function removes -character.

Isn't that simply a php-bug?

checked the changelogs at http://www.php.net/ ?

[royvarley]

Agreed that's what it should be.

Not sure about your conclusion but it could well be so!

I'd like to know what the raw xml looks like for the working tests that Ritesh did using the tunnel. Or even the same set of translations.

[royvarley]

stripslashes did the right thing. I rather think that the should have been \ in the original xml.

[peterlaursen]

Quote:

I rather think that the should have been \ in the original xml.

OK – it seems that you know what you are doing 😀

I thought that stripslashes() should work on / and not .

But rather strange then that Ritesh didn't get an error with your data and I did not either with my data!

[peterlaursen]

your ar right as far as stripslashes goes:

http://www.php.net/manual/en/function.stripslashes.php

is the php directive magic_quotes set to “on” with the php configuration ?

from php docs:

magic_quotes_gpc boolean

Sets the magic_quotes state for GPC (Get/Post/Cookie) operations. When magic_quotes are on, all ' (single-quote), ” (double quote), (backslash) and NUL's are escaped with a backslash automatically.

magic_quotes_runtime boolean

If magic_quotes_runtime is enabled, most functions that return data from any sort of external source including databases and text files will have quotes escaped with a backslash.

[royvarley]

peterlaursen wrote on Aug 3 2005, 01:46 AM:

OK – it seems that you know what you are doing 😀

[post=”6707″]<{POST_SNAPBACK}>[/post]

I wouldn't go that far!

stripslashes converts things like ' into '

Yes very odd that Ritesh and you have both not seen the problem. Obviously something peculiar about my setup – but I can't see what it could be right now.

[royvarley]

magic_quotes_gpc is off.

[peterlaursen]

well, maybe the php tunneling file assumes another stripslashes php-configuration.

[peterlaursen]

I have to go for an hour or two. But I could easily change my php-configuration and test with mq = “off” tonight.

I'll look into the thread later to see if there is a need for it.

[Ritesh]

royvarley wrote on Aug 2 2005, 03:58 PM:

magic_quotes_gpc is off.

[post=”6710″]<{POST_SNAPBACK}>[/post]

Whats is this flag all about?

Quote:

well, maybe the php tunneling file assumes another stripslashes php-configuration.

I think you are correct 🙁

[Ritesh]

peterlaursen wrote on Aug 2 2005, 04:01 PM:

I have to go for an hour or two.  But I could easily change my php-configuration and test with mq = “off” tonight.

[post=”6712″]<{POST_SNAPBACK}>[/post]

Waiting for your results 🙂

I will try it out myself tomorrow at work!

[royvarley]

Being 2:00am, I'm going to bed!

I'll catch up tomorrow – goodnight!

[royvarley]

Good Morning!

Peter – you're right – it's the handling of the magic quotes variable being on or off. I should have twigged when I was saying that the should be \. If magic quotes were on it would have changed to \ and the synch would work.

I changed the code to:

if (get_magic_quotes_gpc()) {

$xmlrecvd = stripslashes ( urldecode ( $_POST['textfield'] ) );

} else {

$xmlrecvd = urldecode ( $_POST['textfield'] );

}

And that works.

SQLyog folks will have to check that that is all that's required – there may be other parts of the code that need attention.

… to breakfast…

[peterlaursen]

@royvarley

it is always a pleasure supporting people who themselves can contribute to the solution! 😀

But I don't know if that is all there is to it. Actually I can tell you that I have been able to reproduce the problem on my local installation – NO MATTER magic_quotes settings. On my webhost I can't reproduce!!

But your idea of turning on php logging was perfect! I think they have all needed info now. And since you've found your solution there no no hurry. better RIGHT tthan FAST!

[peterlaursen]

I confirm that your code-fix also solves the problem on my local installation! 😀

Now I will check whether it also fixes that other issue that I had.

That would be nice! Actually until now I have believed that it was a php5-related problem only, but hope that this is it!

[peterlaursen]

YUP!

that other problem of mine is also fixed by your code-tweak! 😀

[royvarley]

Excellent!

All-in-all a successful night's work!

[peterlaursen]

it was more than just work … it was TEAMWORK! 😀

[Ritesh]

Great 😀

I will release BETA 8 today with the code-fix. I will also add a check in PHP Tunneler so that starting from v4.1 BETA 8, you would require the updated SQLyogTunnel.php.

[Author]

Posts