| v 1.35 - Relationship Calculated Field Escaping [message #1325] |
Thu, 01 May 2008 01:22  |
ljkbrost
Messages: 59
Registered: April 2006
|
Member |
|
|
Hi,
When creating a relationship with a calculated field, such as:
CONCAT(employee_fname, " ", employee_lname) AS employee_name
Below is the output of the exported dictionary definition:
// parent relationship details
$this->parent_relations[]= array(
'parent' => 'pay_employee',
'parent_field' => CONCAT(employee_fname, \" \", employee_lname) AS employee_name, employee_title',
'fields' => array('employee_id' => 'employee_id'));
When the framework executes the SQL for the calculated field it complains about the extra \" \" quotes.
Looking at the code in dict_table_s02.class.inc the problem is the addslashes(...) function call. It is doing something good but has the unintended consequences of breaking things.
I have played around with the various stripslashes, addslashes, and mysql_escape_string functions to see if I could create a patch for this but I was unable to.
A solution that does work for my case is to change the exterior single quotes to double quotes, change my SQL to use single quotes, and remove the addslashes(...) call. Unfortunately this will break it in the case where the SQL uses double quotes.
Another iteration on a solution would be to remove the addslashes(...), trust the input is valid:
1) If there are single quotes in the SQL use double quotes in the template.
2) If there are double quotes in the SQL use single quotes in the template.
3) If there are mixed quotes in the SQL, don't accept the input.
It would be a real nice to have if the was a way to qualify the SQL before actually saving it. I think this would be very difficult to do and increase the code complexity quite a bit.
Sorry I don't have a patch for this issue. I think it's going to have to be a policy rule versus something software can enforce.
Cheers,
Kyle Brost
----
www.softelephant.com
|
|
|
|
| Re: v 1.35 - Relationship Calculated Field Escaping [message #1326 is a reply to message #1325] |
Thu, 01 May 2008 05:15  |
AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
The problem is that your CONCAT function contains double quotes around the space character instead of single quotes. In standard SQL a single-quoted string is taken for a value while a double quoted string is taken as a field name. That is why you get the error message
- unknown column ' ' in 'field list'
Change those double quotes to single quotes, export the table definition, then try running it again. It should not fail this time.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
