Magento 2 - SQL Injection

Question

How to avoid sql injection when running a native sql query with parameter input?

e.g.

$sql = "Select * from eav_attribute where attribute_id = {$attribute_id} ";
$result = $this->_connection->fetchRow($sql);

• Bryan 'BJ' Hoffpauir Jr.: Phillip's answer should cover the specific question that you asked as well serve as a good reference for any future database access topics that you might think of while customizing your magento logic. But I did want to applaud you for thinking ahead about security concerns and specifically about preventing SQL injection attempts. The Magento team has an amazing job focusing on security topics as you dig in to the CodeBase you'll find that many of the concerns that you have our address somewhere or another, but it's never a bad idea to post questions hear about security if ever you are unsure!
• philwinkle: Thanks BJ - wonderful and welcoming response. When in doubt, ask! Thanks ZZpaul

Answer 1

The most correct answer is "don't directly query the database" - you should be using the ORM which would protect you in these situations. Especially when grabbing data out of the EAV tables.

The longer answer is that to do this safely you need to bind the query parameters to the query with Zend_Db_Select's bind rather than using a full SQL statement:

$query = $this->_connection->select()->from('eav_attribute')->where('attribute_id=?', $attributeId);
$result = $this->_connection->fetchAll($query);

For more information see answers like this: https://magento.stackexchange.com/a/103038/336

• fotfs: is there an official document to describe the API that handle the ORM?
• philwinkle: In short it's basically just the Zend_Db_Select API from ZF1 framework.zend.com/manual/1.10/en/zend.db.select.html
• philwinkle: The Magento ORM is woefully lacking in documentation