When joining tables, I use SQL models of the Zend Framework. As example I modified my actual code, but I think you will get the point:
$this->getSelect()->join( array('sections' => $sectionsTableName), 'main_table.banner_id = pages.banner_id', array() ) ->where("sections.section= '$section' OR sections.section = '0' OR (sections.section = '6' AND ? LIKE main_table.url)",$url) ->group('main_table.banner_id');
The page is loaded with ajax and $section parameter is sent as GET parameter (
Now here is the problem if somebody perform something like this:
www.example.com/controllerName/index/display/3?paremeter1=example&url=(SELECT 3630 FROM(SELECT COUNT(*),CONCAT(0x7170786a71,(SELECT (ELT(3630=3630,1))),0x717a716b71,FLOOR(RAND(0)*2))x FROM INFORMATION_SCHEMA.CHARACTER_SETS GROUP BY x)a)
In this way user can dump entire database. The data won't be displayed, but still SQL will perform dump which can cause sql overload.
- What is the best way to prevent such scenario?
- Now I am worried for previous customers. Is with this code possible to make even more risk action, like delate or alter table? I guess not because you can not put any other statement than SELECT inside subselect so DELETE would produce sql syntax error. Am I right?
UPDATE: My example is not proper illustration of SQL injection because there is ' sign arround $sections and thus it won't be possible to make injection. Anyway this would be possible when expecting integer value and when you don't filter integer input. See my comment below.