php - How to prevent SQL injections in manually created queries?

Question

I am using cakephp and below query has sql injection that i know. But the question is how to fix this in same query . I dont want to use other method. Please dont unvoted it

Search->query("select * from subcategories where subcat_name like '%".$_GET['searchkey']."%' and subcat_status='active' ");

Answer 1

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') . '%')
);

• Cookbook > Models > Retrieving Your Data > Prepared Statements
• API > DboSource::fetchAll()

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()