[sclug] URL encoding/decoding question
pieter claassen
pieter at claassen.co.uk
Thu Feb 23 22:37:52 UTC 2006
Raz,
the issue regarding mysql not interpreting prepared statements:
[23 Feb 17:28] Mark Matthews
This is a known issue with the debian-provided builds of MySQL-4.1 (see
the JDBC forums at http://forums.mysql.com/ for more reports).
The issue is listed in the bug tracker for Ubuntu
https://launchpad.net/distros/ubuntu/+source/mysql-dfsg-4.1/+bug/3448
but it appears upstream as well in Debian.
The only workaround (until the Debian folks figure out what's wrong
with their build) is to add "useServerPrepStmts=false" to your JDBC URL
to tell the JDBC driver not to use server-side prepared statements.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Attached is the raw dump of a tethereal session as well as a text dump
of the conversation only.
I did two round trip submissions and this is what was logged by the db :
060223 19:20:25 6 Query SET autocommit=1
6 Query SELECT data FROM content WHERE
id='header.slogan' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.company' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.services' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.products' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.contact' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.logout' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.login' and lang='en'
6 Query UPDATE content set data='€'
where id='hardware.product2.name' and lang='en'
6 Query SELECT data FROM content WHERE
id='footer.bar' and lang='en'
6 Query SET autocommit=1
060223 19:20:28 6 Query SET autocommit=1
6 Query SELECT data FROM content WHERE
id='header.slogan' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.company' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.services' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.products' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.contact' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.logout' and lang='en'
6 Query SELECT data FROM content WHERE
id='menu.login' and lang='en'
6 Query UPDATE content set data='?' where
id='hardware.product2.name' and lang='en'
6 Query SELECT data FROM content WHERE
id='footer.bar' and lang='en'
6 Query SET autocommit=1
The code produced by the following bit of java is below:
System.out.println("decoder started");
byte[] wad = data.getBytes("UTF-8");
for(int i=0; i<wad.length; i++)
System.out.println(Integer.toHexString( (wad[i] & 0xFF) ));
return data;
decoder started
26
65
75
72
6f
3b
decoder started
c3
a2
c2
82
c2
ac
AND I AM STILL USING THE FOLLOWING CODE TO PRINT TO THE BROWSER:
while (rs.next()){
data=rs.getString(1);
count=count+1;
}
Further to your comments:
> > OK, this doesn't make sense. If you had already switched to BLOB, then
> > why did you care what interpretation the database was applying? What
> > would be the point in switching the character encoding in the on-disk
> > files and in playing with session character set/encoding parameters if
> > you were already using BLOB anyway?
It struck me that the java connector deducted a lot of things automatically based on db settings so I
thought it might (and still might be) getting the returned data encoding wrong, because the DB is telling it
that the data is latin1.
> > - You are now (and have been for at least a couple of steps) storing the
> > data as a BLOB?
> >
Yes, the table has been created as utf8 encoded and the server default-character-set=utf8 (not that it matters
as you already mentioned because as a blob, the data should come out as whatever it went in).
> >
> > - You are now handing the data to, and receiving it from, the database
> > as sequences of bytes (byte[]/InputStream/OutputStream) not sequences of
> > characters (char[]/String/Reader/Writer)?
No, this is still something I have to try but I expect we might have
enough data in the ethereal dumps now to identify exactly where the
problem is occurring.
> > This doesn't mean that you can't make things worse by tinkering
> though :-)
Tell me about it!
>
>
> > Does this mean that we can get back to solving your problem now?
> >
> > (Please, no more arbitrarily replacing the building's foundations while
> > we're trying to debug the alarm system :-))
> >
I swear not to upgrade the bloody database again (not even to postgresql) while we are trying to sort the character
encoding thing out ;-)
Cheers,
Pieter
On Thu, 2006-02-23 at 15:24 +0000, Roland Turner (SCLUG) wrote:
> On Thu, 2006-02-23 at 14:07 +0000, Pieter Claassen wrote:
> >
> > > Umm, how long ago; before or after reloading the database to change
> > > encoding?
> > >
> > This was before reloading it so you might be right that an alter statement
> > might not be enough and that data will have to be reloaded.
>
> {{ I understand that at this exact second, stuff is broken; my next few
> questions relate to the prior period. }}
>
> OK, this doesn't make sense. If you had already switched to BLOB, then
> why did you care what interpretation the database was applying? What
> would be the point in switching the character encoding in the on-disk
> files and in playing with session character set/encoding parameters if
> you were already using BLOB anyway?
>
> I also thought you said that you had actually reloaded. It should not
> have been possible to switch from TEXT to BLOB by having an ALTER TABLE
> change the type of a column without reloading the table anyway. Can we
> make certain that we're talking about the same thing please:
>
> - You are now (and have been for at least a couple of steps) storing the
> data as a BLOB?
>
> - You are now handing the data to, and receiving it from, the database
> as sequences of bytes (byte[]/InputStream/OutputStream) not sequences of
> characters (char[]/String/Reader/Writer)?
>
> > The issue at the moment is that mysql is broken and I cannot recreate the
> > round trip until I have fixed it.
>
> Understood.
>
> > I upgraded mysql to 4.1 because it was
> > clear that it had better unicode support that 4.0 (which under
> > ubuntu/debian does not have utf8 support compiled in).
>
> OK, this may have been an unfortunate choice. Given that you're already
> using BLOB, you _really_ don't care about the database's character
> set/encoding support.
>
> This doesn't mean that you can't make things worse by tinkering
> though :-)
>
> > Tomcat seems to use preparedstatement internally for authentication so
> > there is no real option other than solving the problem.
> >
> >
> > > You've misunderstood, it's not a question of not _supporting_ it,
> > > prepared statement execution is typically _faster_ than than using the
> > > query cache and, as a result, MySQL doesn't even refer to its query
> > > cache when you are executing a prepared statement.
> > >
> >
> > As I mentioned above, mysql-server 4.1.12-1ubuntu3.1 with
> > mysql-connector-java-[3.1.12|5.0.0-beta|5.0.0-nightly-20060223] fails to
> > substitute the variable values in the preparedstatement line.
>
> OK. Have you done this with a minimal test? In particular, have you done
> it where you are _not_ tweaking any character set/encoding related
> settings on the database? It is entirely possible that you've changed
> the query encoding behind the driver's back and that it is therefore
> mis-recognising the characters in the query and thus unable to do
> anything with it.
>
> Is there any reason for not reverting to MySQL 4.0? Is there any reason
> that you're not using a mysql-connect-java-4.1.* with 4.1?
>
> > setString(...) fails.
>
> What error/exception are you getting when you call setString()? (If
> none, then it isn't failing.)
>
> > I suspect that it is because it is caching the
> > prepare statement and then when executing it (pulls the statement from
> > the cache)
>
> You appear to be mixing two seperate concepts.
>
> - MySQL's query cache is a server-side mapping from literal
> non-parametric query text to a result set. To use the query cache, the
> entire text of the query must be transmitted to the server on each
> invocation, must be character-for-character identical to one that is
> already in the cache and must not have been invalidated by an update to
> one or more source tables.
>
> - MySQL also maintains prepared (compiled) statements, parametric or
> otherwise, per-session. The documentation does not indicate that result
> set is cached. When you execute a PreparedStatement, only a handle is
> sent to the server, plus a binary presentation of any arguments required
> to satisfy the parameters.
>
> My point is that "executing it (pulls the statement from the cache)"
> suggests to me that you may be confusing the query cache with the
> per-session collection of prepared statements. The documentation does
> not appear to use the word "cache" when describing the latter.
>
> > tries to execute the literal SQL query.
>
> Do you have any evidence for this? (You've not provided any error
> messages.)
>
> > The MySQL recommended
> > methods to disable sql caching still results in mysql reporting that the
> > cach is enabled but just 0 bytes large. Not sure what that means.
>
> Presumably an implementation hack. If it's size is 0, it can't very well
> be holding anything.
>
> > 9 Prepare [1] SELECT SQL_NO_CACHE data FROM content WHERE lang=?
> > 9 Execute [1] SELECT SQL_NO_CACHE data FROM content WHERE lang=?
>
> I'm not sure what you're showing here. Is it possible that you're
> assuming that because the query is being logged in parametric form that,
> therefore, the parameters have not been satisfied? If so, note that this
> is not possible; if there are unsatisfied parameters, execute() will
> fail and tell you that this is so.
>
> > > I don't understand why you are raising this now though; what has it to
> > > do with your character encoding problem?
> >
> >
> > > Where are those Ethereal dumps? These will tell us exactly what's going
> > > on, rather than messing about with random tinkering. (Empirical method:
> > > measure first.)
> >
> > I will produce them (and have been trying to) as soon as I have a db that
> > can do preparedstatement otherwise they are meaningless.
> >
> > I just tested and the problem does not exist in MySQL 5.0.18.
>
> Does this mean that we can get back to solving your problem now?
>
> (Please, no more arbitrarily replacing the building's foundations while
> we're trying to debug the alarm system :-))
>
> - Raz
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: euro_dump.raw
Type: application/octet-stream
Size: 14230 bytes
Desc: not available
Url : http://lists.tmdg.co.uk/pipermail/sclug/attachments/20060223/0671d92a/euro_dump-0001.obj
-------------- next part --------------
POST /content/index.jsp HTTP/1.1
Host: itx.claassen.co.uk
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-GB; rv:1.7.12) Gecko/20051010 Firefox/1.0.7 (Ubuntu package 1.0.7)
Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
Accept-Language: en-gb,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
Referer: http://itx.claassen.co.uk/content/index.jsp
Cookie: JSESSIONID=1CAEAA86BACE9FC3CC41307FAC3C828C
Content-Type: application/x-www-form-urlencoded
Content-Length: 72
id=hardware.product2.name&lang=en&data=%26euro%3B&submit=Update+database
HTTP/1.1 200 OK
Date: Thu, 23 Feb 2006 18:20:25 GMT
Server: Apache/2.0.54 (Ubuntu) mod_jk/1.2.15 mod_perl/2.0.1 Perl/v5.8.7
Content-Length: 3420
Keep-Alive: timeout=15, max=100
Connection: Keep-Alive
Content-Type: text/html;charset=UTF-8
<html>
<head><title>Musmato</title>
<link rel="stylesheet" href="/resources/style.css" type="text/css"/>
</head>
<body>
<table bgcolor="#ffffff" align="center" width="800" cellpadding="10">
<tr><td>
<table bgcolor="#dd1100" align="center" width="100%" cellpadding="0" cellspacing="0" border="0" valign="top">
<!--
<colgroup>
<col align="center" width="80"/><col align="left"/>
</colgroup>
-->
<tr>
<td>
<table bgcolor="#dd1100" border="0" width="100%" cellspacing="0" cellpadding="0">
<tr valign="top">
<td>
<div class="bordered">
<a class="clean" href="/">
<img class="clean" src="/resources/name_only.png"/>
</a>
<br/>
<font size="+1" color="white" >Low cost computers and support<a href="/content?id=header.slogan&lang=en">[edit]</a></font>
</div>
</td>
<td>
<div
align="right">
<img class="clean" src="/resources/logo_square.png"/><br>
<br>
pieter
</div>
</td>
</tr>
</table>
</td>
</tr>
<!--spacer row -->
<tr>
<td> </td>
</tr>
<tr>
<td><a class="button" href="/aboutus">Company<a href="/content?id=menu.company&lang=en">[edit]</a></a>
<a class="button" href="/services">Services<a href="/content?id=menu.services&lang=en">[edit]</a></a>
<a class="button" href="/hardware">Products<a href="/content?id=menu.products&lang=en">[edit]</a></a>
<a class="button" href="/contact">Contact<a href="/content?id=menu.contact&lang=en">[edit]</a></a>
<a class="button" id="r_button" href="/login/logout.jsp">Logout<a href="/content?id=menu.logout&lang=en">[edit]</a></a>
<a class="button" id="r_button" href="/login/redirect.jsp">Login<a href="/content?id=menu.login&lang=en">[edit]</a></a>
</td>
</tr>
<tr>
<td valign="top" align="left">
<table border="0" cellspacing="0" cellpadding="10" width="100%" bgcolor="#ffffff">
<tr><td>
<h1>DATABASE UPDATED</h1>
<div class="full">
<h1>Edit content page<h1>
<h3>Access to this page is restricted to Musmato administrators</h3>
<form action="/content/index.jsp" method="post">
Content ID = <b>hardware.product2.name</b>
<input type="hidden" name="id" value=hardware.product2.name>
<br>
Language = <select name="lang">
<option SELECTED value="en">English</option>
<option value="nl">Nederlands</option>
<option value="de">Deutch</option>
</select>
<p>
<h1>TEXT IN CLEAR</h1>
<textarea name=data rows=20 cols=80>€</textarea>
<!--
<p>
<h1>TEXT URLDECODED </h1>
<textarea name=data1 rows=20 cols=80>€</textarea>
-->
<input type="submit" value="Update database" name="submit">
</form>
</div>
<!-- first close the main table on the main page -->
</td>
</tr>
</table>
<!-- now close the table that contains the header as well -->
</td>
</tr>
</table>
<br>
<!-- footer table -->
<table bgcolor="#c7c7c7" width="100%" border="0" cellspacing="5" cellpadding="0">
<tr>
<td valign="middle">
<a href="/">Home</a> | <a href="/terms">Terms and Conditions
</a> | <a href="/faq">FAQ</a> |
<a href="/gallery">Gallery</a> | <a href="/case">Case studies</a><a href="/content?id=footer.bar&lang=en">[edit]</a>
</td>
<td align="right"><div align="right">
<a class="clean" href="http://itx.claassen.co.uk/content/index.jsp?lang=en"><img src="/resources/en.png"></a> <a class="clean" href="http://itx.claassen.co.uk/content/index.jsp?lang=nl"><img src="/resources/nl.png"></a> <a class="clean" href="http://itx.claassen.co.uk/content/index.jsp?lang=de"><img src="/resources/de.png"></a></div>
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>
GET /favicon.ico HTTP/1.1
Host: itx.claassen.co.uk
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-GB; rv:1.7.12) Gecko/20051010 Firefox/1.0.7 (Ubuntu package 1.0.7)
Accept: image/png,*/*;q=0.5
Accept-Language: en-gb,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
Cookie: JSESSIONID=1CAEAA86BACE9FC3CC41307FAC3C828C
HTTP/1.1 404 /favicon.ico
Date: Thu, 23 Feb 2006 18:20:26 GMT
Server: Apache/2.0.54 (Ubuntu) mod_jk/1.2.15 mod_perl/2.0.1 Perl/v5.8.7
Content-Length: 988
Keep-Alive: timeout=15, max=99
Connection: Keep-Alive
Content-Type: text/html;charset=utf-8
<html><head><title>Apache Tomcat/5.5.12 - Error report</title><style><!--H1 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:22px;} H2 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:16px;} H3 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:14px;} BODY {font-family:Tahoma,Arial,sans-serif;color:black;background-color:white;} B {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;} P {font-family:Tahoma,Arial,sans-serif;background:white;color:black;font-size:12px;}A {color : black;}A.name {color : black;}HR {color : #525D76;}--></style> </head><body><h1>HTTP Status 404 - /favicon.ico</h1><HR size="1" noshade="noshade"><p><b>type</b> S
tatus report</p><p><b>message</b> <u>/favicon.ico</u></p><p><b>description</b> <u>The requested resource (/favicon.ico) is not available.</u></p><HR size="1" noshade="noshade"><h3>Apache Tomcat/5.5.12</h3></body></html>
POST /content/index.jsp HTTP/1.1
Host: itx.claassen.co.uk
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-GB; rv:1.7.12) Gecko/20051010 Firefox/1.0.7 (Ubuntu package 1.0.7)
Accept: text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
Accept-Language: en-gb,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
Referer: http://itx.claassen.co.uk/content/index.jsp
Cookie: JSESSIONID=1CAEAA86BACE9FC3CC41307FAC3C828C
Content-Type: application/x-www-form-urlencoded
Content-Length: 71
id=hardware.product2.name&lang=en&data=%E2%82%AC&submit=Update+database
HTTP/1.1 200 OK
Date: Thu, 23 Feb 2006 18:20:28 GMT
Server: Apache/2.0.54 (Ubuntu) mod_jk/1.2.15 mod_perl/2.0.1 Perl/v5.8.7
Content-Length: 3420
Keep-Alive: timeout=15, max=98
Connection: Keep-Alive
Content-Type: text/html;charset=UTF-8
<html>
<head><title>Musmato</title>
<link rel="stylesheet" href="/resources/style.css" type="text/css"/>
</head>
<body>
<table bgcolor="#ffffff" align="center" width="800" cellpadding="10">
<tr><td>
<table bgcolor="#dd1100" align="center" width="100%" cellpadding="0" cellspacing="0" border="0" valign="top">
<!--
<colgroup>
<col align="center" width="80"/><col align="left"/>
</colgroup>
-->
<tr>
<td>
<table bgcolor="#dd1100" border="0" width="100%" cellspacing="0" cellpadding="0">
<tr valign="top">
<td>
<div class="bordered">
<a class="clean" href="/">
<img class="clean" src="/resources/name_only.png"/>
</a>
<br/>
<font size="+1" color="white" >Low cost computers and support<a href="/content?id=header.slogan&lang=en">[edit]</a></font>
</div>
</td>
<td>
<div
align="right">
<img class="clean" src="/resources/logo_square.png"/><br>
<br>
pieter
</div>
</td>
</tr>
</table>
</td>
</tr>
<!--spacer row -->
<tr>
<td> </td>
</tr>
<tr>
<td><a class="button" href="/aboutus">Company<a href="/content?id=menu.company&lang=en">[edit]</a></a>
<a class="button" href="/services">Services<a href="/content?id=menu.services&lang=en">[edit]</a></a>
<a class="button" href="/hardware">Products<a href="/content?id=menu.products&lang=en">[edit]</a></a>
<a class="button" href="/contact">Contact<a href="/content?id=menu.contact&lang=en">[edit]</a></a>
<a class="button" id="r_button" href="/login/logout.jsp">Logout<a href="/content?id=menu.logout&lang=en">[edit]</a></a>
<a class="button" id="r_button" href="/login/redirect.jsp">Login<a href="/content?id=menu.login&lang=en">[edit]</a></a>
</td>
</tr>
<tr>
<td valign="top" align="left">
<table border="0" cellspacing="0" cellpadding="10" width="100%" bgcolor="#ffffff">
<tr><td>
<h1>DATABASE UPDATED</h1>
<div class="full">
<
h1>Edit content page<h1>
<h3>Access to this page is restricted to Musmato administrators</h3>
<form action="/content/index.jsp" method="post">
Content ID = <b>hardware.product2.name</b>
<input type="hidden" name="id" value=hardware.product2.name>
<br>
Language = <select name="lang">
<option SELECTED value="en">English</option>
<option value="nl">Nederlands</option>
<option value="de">Deutch</option>
</select>
<p>
<h1>TEXT IN CLEAR</h1>
<textarea name=data rows=20 cols=80>......</textarea>
<!--
<p>
<h1>TEXT URLDECODED </h1>
<textarea name=data1 rows=20 cols=80>......</textarea>
-->
<input type="submit" value="Update database" name="submit">
</form>
</div>
<!-- first close the main table on the main page -->
</td>
</tr>
</table>
<!-- now close the table that contains the header as well -->
</td>
</tr>
</table>
<br>
<!-- footer table -->
<table bgcolor="#c7c7c7" width="100%" border="0" cellspacing="5" cellpadding="0">
<tr>
<td valign="middle">
<a href="/">Home</a> | <a href="/terms">Terms and Conditions<
/a> | <a href="/faq">FAQ</a> |
<a href="/gallery">Gallery</a> | <a href="/case">Case studies</a><a href="/content?id=footer.bar&lang=en">[edit]</a>
</td>
<td align="right"><div align="right">
<a class="clean" href="http://itx.claassen.co.uk/content/index.jsp?lang=en"><img src="/resources/en.png"></a> <a class="clean" href="http://itx.claassen.co.uk/content/index.jsp?lang=nl"><img src="/resources/nl.png"></a> <a class="clean" href="http://itx.claassen.co.uk/content/index.jsp?lang=de"><img src="/resources/de.png"></a></div>
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>
GET /favicon.ico HTTP/1.1
Host: itx.claassen.co.uk
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-GB; rv:1.7.12) Gecko/20051010 Firefox/1.0.7 (Ubuntu package 1.0.7)
Accept: image/png,*/*;q=0.5
Accept-Language: en-gb,en;q=0.5
Accept-Encoding: gzip,deflate
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7
Keep-Alive: 300
Connection: keep-alive
Cookie: JSESSIONID=1CAEAA86BACE9FC3CC41307FAC3C828C
HTTP/1.1 404 /favicon.ico
Date: Thu, 23 Feb 2006 18:20:29 GMT
Server: Apache/2.0.54 (Ubuntu) mod_jk/1.2.15 mod_perl/2.0.1 Perl/v5.8.7
Content-Length: 988
Keep-Alive: timeout=15, max=97
Connection: Keep-Alive
Content-Type: text/html;charset=utf-8
<html><head><title>Apache Tomcat/5.5.12 - Error report</title><style><!--H1 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:22px;} H2 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:16px;} H3 {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;font-size:14px;} BODY {font-family:Tahoma,Arial,sans-serif;color:black;background-color:white;} B {font-family:Tahoma,Arial,sans-serif;color:white;background-color:#525D76;} P {font-family:Tahoma,Arial,sans-serif;background:white;color:black;font-size:12px;}A {color : black;}A.name {color : black;}HR {color : #525D76;}--></style> </head><body><h1>HTTP Status 404 - /favicon.ico</h1><HR size="1" noshade="noshade"><p><b>type</b> S
tatus report</p><p><b>message</b> <u>/favicon.ico</u></p><p><b>description</b> <u>The requested resource (/favicon.ico) is not available.</u></p><HR size="1" noshade="noshade"><h3>Apache Tomcat/5.5.12</h3></body></html>
More information about the Sclug
mailing list