[Solved] How to prevent SQL injections in manually created queries?

by


I dont want to use other method

You should use whatever provides the required functionality, not the method that you like more over others!

Also you should never access superglobals directly in CakePHP, this will only bring you in trouble, especially in unit tests. User the proper abstracted methodes provided by the request object, that is CakeRequest::query().

Cookbook > Controllers > Request and Response objects > Accessing Querystring parameters

Use prepared statements

That being said, use prepared statements, either by passing the values to bind to the second argument of Model::query():

$result = $this->Search->query(
    "select * from subcategories where subcat_name like ? and subcat_status="active"",
    array('%' . $this->request->query('searchkey') . '%')
);

API > Model::query()

or by using DboSource::fetchAll(), which accepts parameters as the second argument too:

$db = $this->Search->getDataSource();
$result = $db->fetchAll(
    "select * from subcategories where subcat_name like ? and subcat_status="active"",
    array('%' . $this->request->query('searchkey') . '%')
);

Escape manually

For the sake of completeness, it’s also possible to manually escape the value via DboSource::value(), however you should avoid constructing query strings that way at all costs, as a small mistake can end up causing an unescaped value to be inserted, thus creating a possible SQL injection vulnerability: 

$searchkey = $this->request->query('searchkey');

$db = $this->Search->getDataSource();
$value = $db->value('%' . $searchkey . '%', 'string');

$result = $this->Search->query(
    "select * from subcategories where subcat_name like $value and subcat_status="active""
);

API > DboSource::value()

2

solved How to prevent SQL injections in manually created queries?