0

I thought Doctrine 2 DBAL prepared statements were safe from SQLi. But I found this confusing bit in the docs:

By default the Doctrine DBAL does no escaping. Escaping is a very tricky business to do automatically, therefore there is none by default. When you use the Doctrine DBAL as standalone, you have to take care of this yourself.

What does that mean exactly? If someone tries to inject malicious code, will the query fail (as opposed to insert escaped) because the RDBMS protects me? Or I'm not protected at all?

I'm using PostgreSQL as my RDBMS.

felipsmartins
  • 136
  • 6
ChocoDeveloper
  • 393
  • 1
  • 4
  • 14
  • You are not protected at all and you shouldn't expect to be, it's your job to secure your application. From [Doctrine's security documentation](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/security.html): "Neither Doctrine DBAL nor ORM can prevent such attacks if you are careless as a developer. This section explains to you the problems of SQL injection and how to prevent them." (read the rest of the article for some solid practices & recommendations). – yannis Aug 09 '13 at 17:21
  • @YannisRizos I'm talking about types. Nowhere in those examples the author used types. But still, thanks for the link, because there he clearly states that the prepared statements' examples are secure, even though he didn't use types. – ChocoDeveloper Aug 09 '13 at 18:28
  • Doctrine DBAL relies on PDO for prepared statements, and yes those are secure(ish) when it comes to escaping strings. Still, you really need to be a bit semi-paranoid and never trust the tools _completely_. Also, please edit your question to mention types explicitly. I know you mentioned them in the title, but they are easy to miss. It doesn't really make much of a difference, but it'll help potentials answerers to give you specific answers. – yannis Aug 09 '13 at 18:31
  • 1
    @YannisRizos I cannot partially trust the tool. It either is secure or it isn't. So either I will have to always escape the values, or never. Doing it only some times will either be a waste of precious time, or insecure, and the resulting code will be awful. – ChocoDeveloper Aug 09 '13 at 18:35

2 Answers2

1

I would like to clarify whether you're just using the DBAL part of Doctrine, or all of it. From my understanding, DBAL is a "lower-level" part of Doctrine, that just focuses on communicating with the database.

If you are using Doctrine with Symfony (the most commonly used way I have seen it used), and have created the appropriate entity / repository / query builder classes, then you should not need to escape any values manually. This is because values entered using those classes would be automatically escaped using prepared statements (behind the scenes, by Doctrine).

Jay Sheth
  • 131
  • 2
  • I'm not using Symfony, only some of its components. I use Doctrine2's ORM, but there are some cases where you just want to run some SQL, so you use only the DBAL. So I need to know whether it's secure. Apparently it is secure when you use prepared statements. Most of the DBAL's methods use prepared statements behind the scenes. But the what the docs say about types and escaping is still kind of confusing, I don't know. – ChocoDeveloper Sep 10 '13 at 16:22
1

I'd wondered as well. Googling a lot I found the answer posted by Benjamin Eberlei himself (Doctrine Project Lead and Contributor):

Hello,

of course DBAL uses PDO internally and escapes parameters such as in your example. I think the docs are messed up here. The Paragraph relates to the two subparagraphs about quote() and quoteIdentifier() and should mention quoting INSIDE sql strings (prepared statements are secure of course!). sorry for the confusion, i should update the docs to be more clear on this.

greetings, Benjamin

On Sat, 1 Jan 2011 15:12:28 -0800 (PST)

felipsmartins
  • 136
  • 6
  • He said "(prepared statements are secure of course!)" but prepared statements are NOT secure! example: $options['site_id'] = '\'; INSERT INTO `an_modules` (`module_id` ,`module_system_name` ,`module_version`)VALUES (NULL , \'test\', \''.date('Y-m-dH:i:s').'\');'; $sql = 'SELECT * FROM mytable WHERE site_id = :site_id'; $stmt->bindValue('site_id', $options['site_id'], \PDO::PARAM_INT); and the result will be "SELECT * FROM mytable WHERE site_id = ''; INSERT INTO `an_modules` (`module_id` ,`module_system_name` ,`module_version`)VALUES (NULL , 'test', '2015-11-2514:49:35');' – vee Nov 25 '15 at 07:59