SQL injection in Symfony/Doctrine

Question

Using parameters instead of placing values directly in the query string is done to prevent SQL injection attacks and should always be done:

... WHERE p.name > :name ...
->setParameter('name', 'edouardo')

Does this mean that if we use parameters like this, we will always be protected against SQL injections? While using a form (registration form of FOS), I put <b>eduardo</b> instead and this was persisted to the database with the tags. I don't really understand why using parameters is preventing against SQL injections...

Why are the tags persisted to the database like this? Is there a way to remove the tags by using Symfony's validation component?

Is there a general tip or method that we should be using before persisting data in the database in Symfony?

Answer 1

Start with reading on what's SQL injection.

SQL injection attack takes place when value put into the SQL alters the query. As a result the query performs something else that it was intended to perform.

Example would be using edouardo' OR '1'='1 as a value which would result in:

WHERE p.name > 'edouardo' OR '1'='1'

(so the condition is always true).

"<b>eduardo</b>" is a completely valid value. In some cases you will want to save it as submited (for example content management system). Of course it could break your HTML when you take it from the database and output directly. This should be solved by your templating engine (twig will automatically escape it).

If you want process data before passing it from a form to your entity use data transformers.

Answer 2

If you use parameters instead of concatenation when creating a request, the program is able to tell SQL keywords and values apart. It can therefore safely escape values that may contain malicious SQL code, so that this malicious does not get executed, but stored in a field, like it should.

HTML code injection is another problem, which has nothing to do with databases. This problem is solved when displaying the value, by using automatic output escaping, which will display <b>eduardo</b> instead of eduardo. This way, any malicious js / html code won't be interpreted : it will be displayed.