[AppDB] Problem with xinha, compile_update_string() and query_parameters()
We have a problem with xinha, compile_update_string() and query_parameters(). Xinha changes double quotes, ampersands, the less than symbol, the greater than symbol and probably others into their HTML Ampersand Character Codes[1]. This conflicts with what mysql_real_escape_string() does and causes query_parameters() to refuse to update the database when there is an one of these characters in a Xinha field. I have investigated this for quite a while now and am unsatisfied with any quick solution that have looked at. [1] http://rabbit.eng.miami.edu/info/htmlchars.html -- Tony Lambregts
Is this the case when inserting data from a xinha editor into the database? Is this being seen only in application related data? Sorry for the time you've spent investigating this, it only took a moment for me to realize what the problem was after knowing the symptoms. I should have caught this earlier. I'm pretty sure this is an easy fix of simply not using compile_update_string() or compile_insert_string(). If you look at the use of these functions you'll see something like: $sBlah = compile_insert(update)_string(...); query_parameters("update/insert ".$sBlah." where '?' ...", a, b, c); The problem is that $sBlah contains characters that are special to query_parameters() like '~', '?', '&'. Because we incorrectly put $sBlah into the format portion of query_parameters(), we insert these special characters into the format parameter of query_parameters(). So, the fix is quite simple, stop using compile_insert_string() and compile_update_string() and let query_parameters() do the work. This way we can be sure that we won't be inserting strings with special formatting characters into the format portion of the query_parameters() call. I'll take care of fixing this as soon as I get back home. Chris On 7/2/06, Tony Lambregts <tony.lambregts(a)gmail.com> wrote:
We have a problem with xinha, compile_update_string() and query_parameters(). Xinha changes double quotes, ampersands, the less than symbol, the greater than symbol and probably others into their HTML Ampersand Character Codes[1]. This conflicts with what mysql_real_escape_string() does and causes query_parameters() to refuse to update the database when there is an one of these characters in a Xinha field.
I have investigated this for quite a while now and am unsatisfied with any quick solution that have looked at.
[1] http://rabbit.eng.miami.edu/info/htmlchars.html
--
Tony Lambregts
Chris Morgan wrote:
Is this the case when inserting data from a xinha editor into the database? Is this being seen only in application related data?
Sorry for the time you've spent investigating this, it only took a moment for me to realize what the problem was after knowing the symptoms. I should have caught this earlier.
I'm pretty sure this is an easy fix of simply not using compile_update_string() or compile_insert_string(). If you look at the use of these functions you'll see something like:
$sBlah = compile_insert(update)_string(...);
query_parameters("update/insert ".$sBlah." where '?' ...", a, b, c);
The problem is that $sBlah contains characters that are special to query_parameters() like '~', '?', '&'. Because we incorrectly put $sBlah into the format portion of query_parameters(), we insert these special characters into the format parameter of query_parameters().
So, the fix is quite simple, stop using compile_insert_string() and compile_update_string() and let query_parameters() do the work. This way we can be sure that we won't be inserting strings with special formatting characters into the format portion of the query_parameters() call.
I'll take care of fixing this as soon as I get back home.
Chris
I was not really comfortable with that solution. compile_update_string was very nice for aligning the field with the value so it looked very clean. The trouble with doing it this way is that it is more prone to errors but if you say that is OK with you then I suppose it will do for me too. Change Log: Fix crash in updateing xinha fields. Files changed: include/testResults.php
So, the fix is quite simple, stop using compile_insert_string() and compile_update_string() and let query_parameters() do the work. This way we can be sure that we won't be inserting strings with special formatting characters into the format portion of the query_parameters() call.
I'll take care of fixing this as soon as I get back home.
Chris
I was not really comfortable with that solution. compile_update_string was very nice for aligning the field with the value so it looked very clean. The trouble with doing it this way is that it is more prone to errors but if you say that is OK with you then I suppose it will do for me too.
I agree about the formatting. It was easier to line things up with the other external call to compile_*_string(). It may seem more prone to errors but this is how pear db, adodb, .net and others recommend performing queries. query_parameters() will ensure that the correct number of tokens is present for the number of variables provided so if there is a mismatch the query won't be executed. I think the formatting you chose looks good and is just as good as what we had before. Chris
We'll want to remove ALL uses of compile_update_string() and compile_insert_string(). I'll take care of this since there are 9 or 10 other instances of it. Chris On Sunday 02 July 2006 5:08 pm, Tony Lambregts wrote:
Chris Morgan wrote:
Is this the case when inserting data from a xinha editor into the database? Is this being seen only in application related data?
Sorry for the time you've spent investigating this, it only took a moment for me to realize what the problem was after knowing the symptoms. I should have caught this earlier.
I'm pretty sure this is an easy fix of simply not using compile_update_string() or compile_insert_string(). If you look at the use of these functions you'll see something like:
$sBlah = compile_insert(update)_string(...);
query_parameters("update/insert ".$sBlah." where '?' ...", a, b, c);
The problem is that $sBlah contains characters that are special to query_parameters() like '~', '?', '&'. Because we incorrectly put $sBlah into the format portion of query_parameters(), we insert these special characters into the format parameter of query_parameters().
So, the fix is quite simple, stop using compile_insert_string() and compile_update_string() and let query_parameters() do the work. This way we can be sure that we won't be inserting strings with special formatting characters into the format portion of the query_parameters() call.
I'll take care of fixing this as soon as I get back home.
Chris
I was not really comfortable with that solution. compile_update_string was very nice for aligning the field with the value so it looked very clean. The trouble with doing it this way is that it is more prone to errors but if you say that is OK with you then I suppose it will do for me too.
Change Log: Fix crash in updateing xinha fields.
Files changed: include/testResults.php
Chris Morgan wrote:
We'll want to remove ALL uses of compile_update_string() and compile_insert_string(). I'll take care of this since there are 9 or 10 other instances of it.
Yes we will have to I did some experimenting and any field that has a & in it will bomb out not just xinha fields. If you want to do this that is fine with me. -- Tony Lambregts
Here is a patch that does all instances and removes compile_update_string() as well. Chris On Monday 03 July 2006 10:52 pm, Tony Lambregts wrote:
Chris Morgan wrote:
We'll want to remove ALL uses of compile_update_string() and compile_insert_string(). I'll take care of this since there are 9 or 10 other instances of it.
Yes we will have to I did some experimenting and any field that has a & in it will bomb out not just xinha fields. If you want to do this that is fine with me.
--
Tony Lambregts
participants (3)
-
Chris Morgan -
Chris Morgan -
Tony Lambregts