I'm setting the sort_handler function so that I can seach by title with the leading "A" or "The" removed.
The query I'm using contains single quote characters and a backslash:

function quakerbib_views_tables_alter(&$tables) {
  $tables['node']['fields']['title']['sort_handler'] = 'views_quakerbib_sort_handler_title';
}

function views_quakerbib_sort_handler_title($field, $info) {
  $orderby = 
    'TRIM(LEADING \'"\'    FROM ' .
    'TRIM(LEADING \'\\\'\' FROM ' .
    'TRIM(' .
    'TRIM(LEADING \'THE \' FROM ' .
    'TRIM(LEADING \'A \'   FROM ' .
    'UPPER(' . $field['queryname'] . ' ))))))';
  return($orderby);
}

This throws an error:

user warning: 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 '\'\"\' FROM TRIM(LEADING \'\\\'\' FROM TRIM(TRIM(LEADING \'THE \' FROM TRIM(L' at line 1 query: SELECT node.nid, node_data_field_shelfmark.field_shelfmark_value AS node_data_field_shelfmark_field_shelfmark_value, node.title AS node_title, node.changed AS node_changed, node_data_field_authors.field_authors_value AS node_data_field_authors_field_authors_value, node_data_field_publication_date.field_publication_date_value AS node_data_field_publication_date_field_publication_date_value FROM node node INNER JOIN users users ON node.uid = users.uid LEFT JOIN content_type_item node_data_field_shelfmark ON node.vid = node_data_field_shelfmark.vid LEFT JOIN content_type_item node_data_field_authors ON node.vid = node_data_field_authors.vid LEFT JOIN content_type_item node_data_field_publication_date ON node.vid = node_data_field_publication_date.vid WHERE (node.type IN ('item')) AND (users.name = 'Central Edinburgh') ORDER BY TRIM(LEADING \'\"\' FROM TRIM(LEADING \'\\\'\' FROM TRIM(TRIM(LEADING \'THE \' FROM TRIM(LEADING \'A \' FROM UPPER(node_title )))))) ASC LIMIT 30, 10 in /var/www/drupal/5/includes/database.mysql.inc on line 172.

The reason for the error is that the single quotes and the backslash in my string are escaped by tablesort_sql() called in _views_query->query().

The attached patch unescapes single quotes and backlashes just after tablesort_sql() is called in _views_query->query(). I don't know enough about SQL injection attacks and views to judge whether this is safe or not; I'm hoping that there is no user string input to the sort routines and that therefore it is OK.

CommentFileSizeAuthor
#3 views-tablesort-unescape2.patch1.58 KBegfrith
views-tablesort-unescape.patch746 bytesegfrith
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

egfrith’s picture

egfrith CreditAttribution: egfrith commented
Status: Active » Needs review
merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Needs review » Needs work

This patch fixes a symptom; the problem is that there is extra escaping. It's not clear to me if the extra escaping is even coming from Views, but I'm highly reluctant to address it this way. It's a hack, and a poor one.

egfrith’s picture

egfrith CreditAttribution: egfrith commented
FileSize
views-tablesort-unescape2.patch1.58 KB

The escaping is coming from tablesort_sql() which passes the SQL code through db_escape_string().

I agree that the proposed patch is a poor hack. I modelled it on the code that comes a few lines later in _views_query->query():

    $replace = array('&gt;' => '>', '&lt;' => '<');
    $query = strtr($query, $replace);

I've produced an alternative patch, which uses tablesort_init() instead of tablesort_sql(). It thereby doesn't pass any queries through db_escape_string(), and avoids the problem without the poor hack. As far as I can tell, it isn't an insecure hack, as ultimately all of the data in $header comes from _views_construct_header() and possibly any sort_handler function, so there is no user input. But I am not a views magician!

egfrith’s picture

egfrith CreditAttribution: egfrith commented
Status: Needs work » Needs review
merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

Ok, that is more interesting and possibly more reliable. Are there a couple of people watching this issue willing to do some table testing with their views to make sure this patch doesn't have unexpected adverse consequences?

And I agree about the security concerns; the source of the data used for Views' tablesorts are not user input in any circumstance I can think of.

egfrith’s picture

egfrith CreditAttribution: egfrith commented
Status: Needs review » Closed (fixed)

I've now moved to D6/Views 2 and am overriding a handler class to achieve the same effect. I've worked out how to write a click_sort() function that includes the SQL query earlier on this issue. There don't seem to be any issues with characters being escaped in D6/Views 2, so I'm closing this issue.