Sensitive To Values?

[lazylester]

Hard to believe, but this:

insert into products values(11,

'Pragmatic Unit Testing',

'rn',

'/images/sk_utc_small.jpg',

'29.95',

'2004-06-18 00:00:00');

produces a mysql error message, which disappears if the contents of the third field ('rn')are changed. Furthermore the error message displayed in sqlyog are garbled (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 ''rn
šœÉˉ̎KŽMIˉ̌

L

‹LNŒŒ Ê' at line 3

(0 ms taken)

I find it hard to believe that mySQL is sensitive to the contents of this field and I'm wondering if it's a mysqlyog bug?

The DDL is:

Table Create Table

---

---

products CREATE TABLE `products` (

`id` int(11) NOT NULL auto_increment,

`title` varchar(100) NOT NULL default '',

`description` text NOT NULL,

`image_url` varchar(200) NOT NULL default '',

`price` decimal(10,2) NOT NULL default '0.00',

`date_available` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

[peterlaursen]

I cannot reproduce this. Tested with direct connection and HTTP-tunnel as well on MySQL 5.0.18.

Pic1 shows that there is no error message.

pic2 shows data after import. The r (return) and n (newline) are displayed as squares in the grid, but in BLOB-viever it is parsed correctly.

Looks like a charset issue. What is the default character set for the server? And which MySQL version? If table has another charset (and has to have) than server default then try SET NAMES with the charset you want to use.

Check your HISTORY tab. Do you find anything like:

Code:

/*[04:09:04][   0 ms]*/ show variables like '%character%'
/*[04:09:04][   0 ms]*/ Set character_set_connection=latin1
/*[04:09:04][   0 ms]*/ Set character_set_results=latin1
/*[04:09:04][   0 ms]*/ Set character_set_client=latin1

at the beginning of HISTORY. Those three are identical to SET NAMES

[peterlaursen]

I can add that I tested SSH-tunnelling as well.

It looks like this character '>' triggers this error on your system or on this particular server on the combination of the two. Any idea why just this character? 😮

Do you have a chance to test with another server and another client machine as well ? cross-test I mean .. to find out if it is a server issue or client issue?

How do you connect? And what server platform?

When you forget we allways ask you these questions:

http://webyog.com/faq/1_26_en.html 😀

and one question more: what is the returns of this: SELECT @@sql_mode;

[lazylester]

peterlaursen wrote on Feb 9 2006, 09:53 PM:

I can add that I tested SSH-tunnelling as well.

Peter I'm using HTTP tunnelling in this case

peterlaursen wrote on Feb 9 2006, 09:53 PM:

It looks like this character '>' triggers this error on your system or on this particular server on the combination of the two.  Any idea why just this character?  😮

You are correct that it's the > character that causes the problem. I can't begin to imagine why this should be.

peterlaursen wrote on Feb 9 2006, 09:53 PM:

Do you have a chance to test with another server and another client machine as well ? cross-test I mean .. to find out if it is a server issue or client issue?

I have just tested with another server, and the problem doesn't appear. I'm not sure that this proves that the problem is with the server. But it certainly could be.

peterlaursen wrote on Feb 9 2006, 09:53 PM:

How do you connect? And what server platform?

I'm using HTTP tunneling. The server platform is Linux 2.6.9, Apache 1.3.34, mySQL 4.1.14

peterlaursen wrote on Feb 9 2006, 09:53 PM:

When you forget we allways ask you these questions

Sorry, and thanks for so politely and tactfully reminding me!

peterlaursen wrote on Feb 9 2006, 09:53 PM:

and one question more: what is the returns of this: SELECT @@sql_mode;

1 row returned. It appears to have one column (@@sql_mode) with no contents.

peterlaursen wrote on Feb 9 2006, 08:25 PM:

Looks like a charset issue. What is the default character set for the server?

I ran “phpinfo()” and the result indicated default_charset has “no value” for local value or master value. That's not very informative is it! If you have a suggestion for retrieving more information please let me know.

In mysqlyog I ran: show variables like '%character%'

and received:

character_set_client latin1

character_set_connection latin1

character_set_database latin1

character_set_results latin1

character_set_server latin1

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

Thanks for your suggestions, Peter. Do you get any further insight from my answers to your questions?

[Ritesh]

Is it possible to give temporary access to that server? I would like to work upon it.

[peterlaursen]

If I get any more insight ….

The important thing is I think that is is HTTP.

looks to me like some protocol error

The http-proctocol converts the data to a stream of octets and must be converted back. So there are chances for some issue wiht the implementation in SQLyog, or with some webserver module (including PHP) or a relay server at the remote network.

it some resemblace with this one:

http://www.webyog.com/forums/index.php?sho…view=getnewpost

But that sort of bits'n'bytes details are not me! Let Ritesh!

[lazylester]

Ritesh wrote on Feb 11 2006, 01:49 AM:

Is it possible to give temporary access to that server? I would like to work upon it.

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

Thanks, Ritesh, I'll send you an email with access information

[Ritesh]

lazylester wrote on Feb 12 2006, 11:21 PM:

Thanks, Ritesh, I'll send you an email with access information

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

Received. We will work on it soon.

[Ritesh]

This is very strange. It works on my 3.23.58 as well as 4.1.7 server. Both using HTTP Tunneling. I am now 100% sure that your PHP processor/server is doing something in between.

I am attaching a new SQLyogTunnel.php which will throw out more debug info (please make sure that the directory has write permission otherwise the logging will fail). It will write down the XML that it gets from SQLyog and also the individual query that it executes.

In the XML input log, there will be a tag which is actually the query we are going to execute. It will be encoded in Base64 as the query has characters which PHP doesn't like. You can decode it using this tool at: http://makcoder.sourceforge.net/demo/base64.php. Check if the query is same as what you have executed?

If you are not sure of how to do all the above, just zip and mail me the yogtunnel.log file and I will take a look into it.

[lazylester]

Thanks, Ritesh,

test tunnel client is installed and I've emailed the resulting log file

Les

[Ritesh]

Now the base64 data that is sent by SQLyog is:

Code:

aW5zZXJ0IGludG8gcHJvZHVjdHMgdmFsdWVzKDExLA0KJ1ByYWdtYXRpYyBVbml0IFRlc3RpbmcnLA0K
1xyXG48L3A JywNCicvaW1hZ2VzL3NrX3V0Y19zbWFsbC5qcGcnLA0KJzI5Ljk1JywNCicyMDA0LTA2LTE4IDAwOjAw
jAwJyk=

Which turns up:

Code:

insert into products values(11,
'Pragmatic Unit Testing',
'rn',
'/images/sk_utc_small.jpg',
'29.95',
'2004-06-18 00:00:00')

at:

http://makcoder.sourceforge.net/demo/base64.php

But if you see the text: Query executed: in the log, the decoded query is incorrect with invalid characters so there is a problem with base64_decode(..) function in PHP.

Can you write a simple PHP script to decode the base64 encoded query and see if its the same as the query or it gets messed up?

[lazylester]

Ritesh wrote on Feb 13 2006, 11:51 PM:

… so there is a problem with base64_decode(..) function in PHP.

Thanks Ritesh,

a few quick thoughts before I dig further…

1. If I were to try to write a base64 decoder I would undoubtedly create more errors than I would solve!!

2. The likelyhood that there is a problem in the PHP base64_decode function seems to me to be very, very low.

3. I looked at the code for the online decode utility that you cite. They too are using the PHP base64_decode function. This suggests the function is OK.

I have to go out now, but I'll consider this further later in the day.

thanks for your help

Les

[peterlaursen]

This script:

Code:

$decode = base64_decode (“aW5zZXJ0IGludG8gcHJvZHVjdHMgdmFsdWVzKDExLA0KJ1ByYWdtYXRpYyBVbml0IFRlc3Rpbmcn
A0K
J1xyXG48L3A JywNCicvaW1hZ2VzL3NrX3V0Y19zbWFsbC5qcGcnLA0KJzI5Ljk1JywNCicyMDA0LTA2LTE4IDAwOjAw
OjAwJyk=”);
echo $decode;
?>

returns

Code:

insert into products values(11, 'Pragmatic Unit Testing', 'rn
šœÉËB‰ÌŽKŽMIËB‰ÌŒ
L ‹LN Œ Œ Ê

However this

Code:

$decode = base64_decode ('VGhpcyBpcyBhbiBlbmNvZGVkIHN0cmluZw==');
echo $decode;
?>

does as php docs at http://www.php.net/manual/en/function.base64-decode.php say!

there are variations of base 64 http://en.wikipedia.org/wiki/Base64

[peterlaursen]

however PHP encode and decode match: try:

Code:

'Pragmatic Unit Testing',
'rn',
'/images/sk_utc_small.jpg',
'29.95',
'2004-06-18 00:00:00')”);
echo $encode;

echo “nn”;

$decode = base64_decode($encode);
echo $decode;

?>

returns

Code:

aW5zZXJ0IGludG8gcHJvZHVjdHMgdmFsdWVzKDExLA0KJ1ByYWdtYXRpYyBVbml0IFRlc3RpbmcnLA0K
w0KPC9wPicsDQonL2ltYWdlcy9za191dGNfc21hbGwuanBnJywNCicyOS45NScsDQonMjAwNC0wNi0xO
AwMDowMDowMCcp insert into products values(11, 'Pragmatic Unit Testing', ' ', '/images/sk_utc_small.jpg', '29.95', '2004-06-18 00:00:00')

[peterlaursen]

@ritesh:

who says PHP is wrong?

where did you get your C-code from?

[lazylester]

peterlaursen wrote on Feb 14 2006, 09:04 AM:

@ritesh:

who says PHP is wrong?

where did you get your C-code from?

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

After reflection, while driving around, I think Ritesh may not be challenging the accuracy of the php base64_decode() function (as I had first understood) but instead he may be looking at the interoperation of base64 encoding on the user's computer and base64 decoding on the server, which brings other factors into play.

Anyway, based on this line of investigation, I'll put up something on my server that may help…. standby…

Les

[peterlaursen]

yes .. do that! PHP is broken on my LINUX at the very moment, so I can't!

but encoding is being done from a compiled binary written in C (SQLyogEnt.exe and SJA.exe). That means that the encoding the written i C. So this code may as well be 'defect'.

It is too easy ('cheap' I would say) just to blame PHP here. and @Ritesh: that fires back on yourself, actually! You should not use it if it does not work! It is your decision to use it!

But I think a buggy C-code is responsible for this issue as well as for the failure to write national European characters (all non-English Latin1 -characters) with HTTP. PHP is used by millions of people everyday. That issue would have been found long before is it was an issue with PHP.

I STILL think a buggy C-code is responsible – and will continue to do so until proven otherwise! 🙄

[lazylester]

I'm sure Ritesh would agree that the PHP base64_decode is solid and not in question, Peter. I think there are other interpretations of his earlier statement. (Divided by a common language?!!)

Anyway, take a look at this:

http://www.thewierdturnpro.com/notrails/base64test.php

and let me know if this facilitates your debugging.

I'm almost out of my depth here, so just let me know if I can make any changes to this little test script to help you.

[peterlaursen]

don't ask me to do that 🙂 Everything that has the slightest resemblance of javascript …

But you are missing some PHP-tags, aren't you ??

I tried to add some around the IF-statement but looks like there is some error in there – PHP does not send anything to Apache when I test it on my local WAMP-stack.

Let Ritesh explain for himself, what he meant. But it does not HELP anyone just to state 'this is a bug with PHP'. Sounds like: 'Not my fault so I don't need to do anything'.

Wrong! He/We must find a solution! That was my point! 😎

And personally I don't believe in a bug with PHP here. Rather a bug somewhere else. Or an issue with base64-'dialects'. Or some kind of initialisation that must be done somewhere.

Hopefully he sleeps sweet now!. He is back at office in 6-7 hours from now!

[lazylester]

peterlaursen wrote on Feb 14 2006, 02:46 PM:

But you are missing some PHP-tags, aren't you ??

You're right, Peter, I just showed the important piece of code, mainly to show the use of stripslashes and htmlentities because it could be confusing if you didn't know exactly how they were used in this script. I left out php tags and html stuff.

I think Ritesh wants to determine my server's response to some coded strings of his own (I'm guessing that's what he's up to anyway)

all the best

Les

[peterlaursen]

I believe there is nothing special with your servers' response. My local php and the php at my webhost does exactly the same with the examples that you posted.

The php_encode() and php_decode() work together – with your examples and with Danish, German, Swedish, Spanish special characters. All the situations where WRITE with SQLyog/ SJA and HTTP-tunnelling fails. I have tested that on my local (php 5.1.1) and my webhost (php 4.4.2). Encoding and decoding again always returns the 'source'.

The 'SQLyog_encode()' and php_decode() does not work together in some special situations! I don't think Ritesh has the power to change (globally) php_decode(). But he can change SQLyog_encode(). And will have to. Or use something else than base64. But I don't believe that is necesary either!

Go ahead and finish your script. It is a wonderful exercise!

[lazylester]

peterlaursen wrote on Feb 14 2006, 04:04 PM:

I believe there is nothing special with your servers' response.  My local php and the php at my webhost does exactly the same with the examples that you posted.

Peter, I think this is new information. You had said in your first or second response to my original post that you could NOT reproduce my result. But if I understand you correctly, you are now saying that you CAN reproduce my result. Perhaps you can clarify?

peterlaursen wrote on Feb 14 2006, 04:04 PM:

Go ahead and finish your script.

I'm not sure what more you would like to see in the script. It's finished and working at the URL I posted, according to my interpretation of Ritesh's request. I assume that you entered text and clicked the encode/decode buttons. Let me know what else would be useful to you, or perhaps we should wait for Ritesh and see if I have written the script he was looking for.

best regards

Les

[peterlaursen]

you are right .. I'm confused now 🙁

hmmm .. I better shut up for a while and rethink it.

But it is not a wrong decoding in PHP. In is the input to php_decode() that is wrong.

And this happens somewhere between when it is logged and decoded.

[Ritesh]

peterlaursen wrote on Feb 14 2006, 04:04 PM:

@ritesh:

who says PHP is wrong?

I am not saying that PHP is wrong. What I am trying to convey is that between base64_decode(…) and actual query execution, the query seems to be changing. As you see the same query works for your system and for me too but it is not working on LazyTesters machine.

[Ritesh]

This gets even more interesting.

The Base 64 Encoded version of the query which is sent by SQLyog (as can been seen in the logs) is:

Code:

aW5zZXJ0IGludG8gcHJvZHVjdHMgdmFsdWVzKDExLA0KJ1ByYWdtYXRpYyBVbml0IFRlc3RpbmcnLA0K
1xyXG48L3A JywNCicvaW1hZ2VzL3NrX3V0Y19zbWFsbC5qcGcnLA0KJzI5Ljk1JywNCicyMDA0LTA2LTE4IDAwOjAw
jAwJyk=

Now if I decode this at: http://makcoder.sourceforge.net/demo/base64.php, it is correctly returning:

Code:

insert into products values(11,
'Pragmatic Unit Testing',
'rn',
'/images/sk_utc_small.jpg',
'29.95',
'2004-06-18 00:00:00')

but if I decode the same text at: http://www.thewierdturnpro.com/notrails/base64test.php, it is decoded as:

Code:

insert into products values(11, 'Pragmatic Unit Testing', 'rn

which is what happens exactly with SQLyogTunnel.php.

I checked the code at makcoder and it goes something like:

Code:

$input = $actionForm->get('input');
 $input = stripslashes($input); //strip slashes
 $input = Main::check_length($input); //checking allowed length of input
               $b64->decode($input);

which translates into something like:

Code:

base64_decode(htmlentities($user_input))

In your sample code, you are doing something like:

Code:

htmlentities(base64_decode($user_input))

which is wrong because the decode function will correctly decode the data and since the data has HTML entities e.g. it messes it up.

So if you change your code to:

Code:

base64_decode(htmlentities($user_input))

, it should work.

I will now take a look into SQLyogTunnel.php implementation to check the order of function execution.

[lazylester]

Ritesh wrote on Feb 14 2006, 11:32 PM:

This gets even more interesting.

Indeed it does!!

First regarding your interpretation of makcoder's code…

You asserted that:

Code:

$input = $actionForm->get('input');
 $input = stripslashes($input); //strip slashes
 $input = Main::check_length($input); //checking allowed length of input
               $b64->decode($input);

translates into something like:

Code:

base64_decode(htmlentities($user_input))

Actually that's not right, Ritesh… stripslashes is not the same as htmlentities at all, or even similar. There's really no reason for makcoder to do a stripslashes at this point because base64-encoded data cannot contain the characters that php escapes when magic quotes are on.

Secondly, you assert that

Code:

htmlentities(base64_decode($user_input))

is wrong because “the decode function will correctly decode the data and since the data has HTML entities e.g. it messes it up.”

Actually I still think it's right (but I'm never dogmatic, and could be pursuaded otherwise!). htmlentities is required simply to display some characters (e.g.

) that the browser would otherwise interpret instead of displaying. So it's just a display function. The reason makcoder does NOT use this, is he renders the results in a form element, which does this function implicitly, and can correctly display these elements instead of interpreting them.</p> <p>Perhaps there's a big clue in the fact that my code produces the same result that sqlyog produces.</p> <p>Just one more point to make… I discovered that magic_quotes_gpc is turned ON on my server. Please consider this in your investigation. (Note, this is why I used stripslashes in my little test script).</p> <p>thanks for your help, I look forward to your comments</p> <p>Les

[Ritesh]

From SQLyogTunnel.php:

Code:

   /* fix in v4.1 that checks for global configuration flag get_magic_quotes_gpc() to stripslashes or not */
   /* fix in 4.2. We have to use rawurldecode instead of urldecode. Urldecode converts + to space which is wrong */  
   if (get_magic_quotes_gpc()) {
       $xmlrecvd = stripslashes ( rawurldecode ( $_POST['textfield'] ) );
   } else {
       $xmlrecvd = rawurldecode ( $_POST['textfield'] );
   }    

I think that holds the key.

Also what happens in your server if you just do base64_decode(…) of:

Code:

aW5zZXJ0IGludG8gcHJvZHVjdHMgdmFsdWVzKDExLA0KJ1ByYWdtYXRpYyBVbml0IFRlc3RpbmcnLA0K
1xyXG48L3A JywNCicvaW1hZ2VzL3NrX3V0Y19zbWFsbC5qcGcnLA0KJzI5Ljk1JywNCicyMDA0LTA2LTE4IDAwOjAw
jAwJyk=

No stripslashes, no htmlentities.

[lazylester]

Hello Ritesh, you can see the result for yourself at http://www.thewierdturnpro.com/notrails/base64test.php

However, I fear I may be confusing you with my code, let me try to clarify…

1. In order to display in html some string that the user enters into the textarea of my script, I must use stripslashes and html entities. However this is ONLY used to display the user's input string, it's not applied before base64 decoding.

2. I was applying htmlentities (only, not stripslashes) to the base64 decoded string. This is required in order to display in html. You can see the result at http://www.thewierdturnpro.com/notrails/base64test.php . As you can see when using your test string, the use of html entities permits the display of something that the browser otherwise cannot display. Look at the html source for the decoded string that is returned, and that the browser cannot fully render.

Les

[lazylester]

Ritesh wrote on Feb 15 2006, 08:28 AM:

Also what happens in your server if you just do base64_decode(…) of:

Code:

aW5zZXJ0IGludG8gcHJvZHVjdHMgdmFsdWVzKDExLA0KJ1ByYWdtYXRpYyBVbml0IFRlc3RpbmcnLA0K
1xyXG48L3A JywNCicvaW1hZ2VzL3NrX3V0Y19zbWFsbC5qcGcnLA0KJzI5Ljk1JywNCicyMDA0LTA2LTE4IDAwOjAw
jAwJyk=

Here's some more insight, Ritesh…

In the string (above) which you asked me to decode, there's a “space” character after XG48L3A . The “space” character is not a legal character in base64-encoded strings. By trial and error I found that this “space” character should be a “+” character for correct decoding. Please see if you can find a coding error in your base64 encoder that would produce this illegal “space” character instead of the correct “+” character.

Since characters are encoded in triplets in base64 encoding, the sequence “/p>” is being encoded as “L3A” instead of “L3A+”.

HTH

Les

[Ritesh]

Yes indeed it should be a PLUS. Your log also confirms that there is a space so I guess there is an error in that.

I will now work on this to see if there is a bug in our BASE64Encoder implementation.

The strange part is that it works on our local PHP as well as PHP system hosted at our webserver.

[peterlaursen]

http://en.wikipedia.org/wiki/Base64

I linked to it before. It describes base64-'dialects'

“UTF-7 introduced a system called Modified Base64. This data encoding scheme is used to encode the UTF-16 used as an intermediate format in UTF-7 into printable ASCII characters. It is a variant of the base64 used in MIME. UTF-7 was intended to allow use of unicode in e-mail without using a separate content transfer encoding. The main difference it has versus the MIME variant base64 is that it does not use the “=” symbol for padding, as that character tends to require a fair amount of escaping. Instead, it pads the octet bits with zeros.”

looks like an issue with the MIME and UTF7 base 64 'dialects' ??

[Ritesh]

lazylester wrote on Feb 10 2006, 01:56 AM:

Hard to believe, but this:

You got a PM!

[lazylester]

Ritesh wrote on Feb 20 2006, 09:52 AM:

You got a PM!

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

Hi Ritesh,

Got the PM and the attached beta code.

Sorry to be the bearer of bad news, but…

I installed the beta 5.1, and lo exactly the same error. Thinking that some component of the previous version hadn't been properly overwritten by the beta code, I uninstalled my previous version (5.01 I think) and uninstalled the beta 5.1. Then RE-installed the beta 5.1. Still the same error… EXACTLY the same, the base64 encoded string is the same as before, with the same character where there should be a “+” character.

Of course, the SQLyogTunnel.php on the server is the same as before (with the logging capability), it didn't change with the beta release. The log corroborates the base64 encoding problem, with the abberant character.

Let me know what further I can do to help debug… were you able to reproduce the problem in your own environment? Peter L. if you're still tracking this thread… can you clarify whether or not you were able to reproduce the problem?

best regards

Les

[Author]

Posts