Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

I want to know db->execute query is safe from injection ?

I need to use other database table ! So I used prepare & execute query , but I would like to know is it safe from sql injection ? If not , what query method should I used ?

Note : This is not current APP database . It is another related database so I can't use phalcon model method

    $this->db1 = $this->getDI()->getShared('db1');
    $data['company'] = $this->db1->prepare("select * from table1 where id = :id");
    $data['company']->execute(array("id"=>$id));


4.1k
Accepted
answer
edited Nov '17

When you use a prepared query like that, it's safe from injection. The initial syntax is parsed, and its operation is decided at that point. The bound parameters are passed separately, and thus don't affect the operation whatsoever. SQL injection is only an issue when you're not escaping user input properly, and it can affect the query operation. For example they might send in a single quote, followed by an OR 1 OR followed by a single quote. Thus making a statement true. Or they might send in a semi colon to execute a new query entirely, then end in a useless query that follows with a single quote and thus makes the later query free of syntax errors. They could also comment it out, etc. Generally, probably 99.999999% of the time, mysqli_real_escape_string is secure and free of any SQL injection vulerabilities. However it is prone to error with charsets issues, and misconfigurations such as the SQL mode, and it's possible there's a version of PHP and/or MySQL version out there which doesn't handle things properly. That's assuming you're using mysqli, rather than PDO as your adapater for PHP and MySQL to communicate. I'm not saying mysqli_real_escape_string is insecure, but what I am saying is it's possible to misuse it in some edge cases. Even in such cases, the chances of someone discovering it are pretty slim, and how to exploit it, to whatever small degree it can be exploited, etc. Generally the concern here is ignorant escaping mechanisms regarding a charset's mutibyte characters.
It might try to escape the first byte, and think it also escaped the second byte, etc.
Might also check out: https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string

For these reasons, prepared queries are thought to be bullet-proof because the operation is already decided without any user-submitted data involved. Only after the query is parsed, is the user's data bound, and thus there's no room for the bound parameters to gain SQL injection power. If the user somehow discovered a PHP or SQL vulnerabilty with bound parameters, the worst that could happen would be that the query would simply fail. They would not gain SQL injection capabilities.

Thus using prepared queries like you are currently doing, preparing a query with only trusted data, and binding the untrusted data afterwards, is safe from SQL injection.

However simply using prepared queries doesn't make a query secure if you're not binding untrusted data properly:

    $data['company'] = $this->db1->prepare("select * from table1 where id = $id");
    $data['company']->execute([]);

The above is insecure, unless $id was typecasted to a number first. Generally even this practice should be avoided and one should take advantage of prepared queries. There's no guarantee some PHP noob won't come later and write something insecure. By setting the example, using prepared queries, it keeps noobs in check as well, so they don't fall into writing bad code. What if someone removed the (int) typecast later, not realizing its impact to the codebase?

Thus prepared queries assume nothing, and leave no room for SQL injections.

TL;DR

What you wrote is safe:

    $this->db1 = $this->getDI()->getShared('db1');
    $data['company'] = $this->db1->prepare("select * from table1 where id = :id");
    $data['company']->execute(array("id"=>$id));

Because it binds all untrusted data separate from the prepared query, leaving no room for SQL injection.

Thank you very much for your excellent explanation !!