SQL QUERY ERROR , alpha-index

A vulnerability scanner I was using picked this up as a potential for a SQL injection point.

index.php?option=com_flexicontent&Itemid=xx&cid=xx&lang=en&view=category&letter="

returns the sql query below

It appears that there is some checking being done, but I wasn't sure if this was something you are aware of.


SQL QUERY ERROR:
SELECT DISTINCT i.*, ie.*, u.name as author, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.alias) ELSE i.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as categoryslug FROM #__content AS i LEFT JOIN #__flexicontent_items_ext AS ie ON ie.item_id = i.id LEFT JOIN #__flexicontent_types AS ty ON ie.type_id = ty.id LEFT JOIN #__flexicontent_cats_item_relations AS rel ON rel.itemid = i.id LEFT JOIN #__categories AS c ON c.id = i.catid LEFT JOIN #__users AS u ON u.id = i.created_by WHERE 1=1 AND rel.catid IN ('79') AND ( i.state IN (1, -5) OR i.created_by = 0 ) AND ( ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2012-12-18 03:28:05' ) OR i.created_by = 0 ) AND ( ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2012-12-18 03:28:05' ) OR i.created_by = 0 ) AND i.sectionid = 1 AND ( i.access <= 0 OR i.created_by = 0 ) AND ( CONVERT (LOWER( i.title ) USING BINARY) REGEXP CONVERT ("^(")" USING BINARY) ) ORDER BY i.title ASC
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 ')" USING BINARY) ) ORDER BY i.title ASC' at line 1 SQL=SELECT DISTINCT i.*, ie.*, u.name as author, ty.name AS typename, CASE WHEN CHAR_LENGTH(i.alias) THEN CONCAT_WS(':', i.id, i.alias) ELSE i.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as categoryslug FROM jos_content AS i LEFT JOIN jos_flexicontent_items_ext AS ie ON ie.item_id = i.id LEFT JOIN jos_flexicontent_types AS ty ON ie.type_id = ty.id LEFT JOIN jos_flexicontent_cats_item_relations AS rel ON rel.itemid = i.id LEFT JOIN jos_categories AS c ON c.id = i.catid LEFT JOIN jos_users AS u ON u.id = i.created_by WHERE 1=1 AND rel.catid IN ('79') AND ( i.state IN (1, -5) OR i.created_by = 0 ) AND ( ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2012-12-18 03:28:05' ) OR i.created_by = 0 ) AND ( ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2012-12-18 03:28:05' ) OR i.created_by = 0 ) AND i.sectionid = 1 AND ( i.access <= 0 OR i.created_by = 0 ) AND ( CONVERT (LOWER( i.title ) USING BINARY) REGEXP CONVERT ("^(")" USING BINARY) ) ORDER BY i.title ASC

Thanks for feedback,

yes i am aware of this, but never hesitate to open such topics and give an information that you have.

Recently i have checked this (alpha-index) is a potential sql injection although not possible, because all letters break down to individual characters, so it will not work, will break the SQL query at most

These changes were made in r1580,
in same revision (r1580), the very recent changes to advanced search feature were fixed

what revision did you test?

Just upgraded to RC9b and still see it. It appears there are a number of characters that cause this error to show up.

I guess my suggestion would be to see about removing the possibility for "special characters" and dumping the SQL error into a log file instead of on the screen.

Yes the SQL error should not be shown,

as it gives out some information,

of course some one that knows that you use flexicontent can just download FLEXIcontent free package and see the DB schema !!

i will remove though, and test more for SQL injection on alpha-index although because letter are break-down it is impossible to do it SQL injection there

ok, i have checked this SQL injection is not possible since characters are break down and used individually, but

- I will also disallow also : ( and )
- characters ' and " are already not allowed

this will prevent the query from breaking

and alpha-index length has maximum of 200 , i will make it 50

about logging the SQL to a log file you are right, in general we should not give out information, although information about DB schema (given by printing the query) can be retrieved anyway

Thanks for your feedback it was useful !!