MagentoWeb Development

Magento direct SQL queries with named binding

By October 12, 2013 2 Comments
Magento direct SQL queries with named binding

If you’ve been coding in Magento long enough, you would have come across a situation where you need to query the database directly. For whatever reason, no judgements.

Well, here is my contribution to save you a lot of time. And this is as secure as using models to interact with your database. If you think otherwise, I’d love to hear your thoughts in the comments section.

Direct safe SQL queries in Magento using named binding for security:

Step 1: Initialize a resource to interact with the database:

$read = Mage::getSingleton( 'core/resource' )->getConnection( 'core_read' ); // To read from the database
$write = Mage::getSingleton( 'core/resource' )->getConnection( 'core_write' ); // To write to the database

 

Step 2: Choose which tables you want to connect with. Using this method also takes care of the problem of prefixed tables on some stores. It’s the safest way to go about this.

$productTable = Mage::getSingleton( 'core/resource' )->getTableName( 'catalog_product_entity' );

 

Step 3: Prepare your query

$query = "SELECT product_id FROM " . $productTable . " WHERE created_at BETWEEN :fromdate AND :todate";
$binds = array(
	'fromdate' => $unsafePostedValue1,
	'todate' => $unsafePostedValue2
);

 

Step 4: Execute your query!

$result = $read->query( $query, $binds );
while ( $row = $result->fetch() ) {
	echo 'Product ID: ' . $row['product_id'] . '<br>';
}

 

Explanation:
Our read resource takes our query and looks for something like :this . It then looks for a bind array which it then maps the value of this to :this . What’s more is that it automatically quotes and prepares all the security measures before executing the query. So you don’t need to quote the query at all! In fact, it will give you an error if you quote the query!

 

Inserting into the database:

Here’s how you insert into the database:

$query = "INSERT INTO " . $productTable . " SET product_id = :product_id";
$binds = array(
	'product_id' => $unsafePostedId
);
$write->query( $query, $binds );

 

Happy coding! 🙂

2 Comments

  • Herve says:

    Direct queries are useful. However, please do not say they are just as safe as using core code.. integrity and compliance with code architecture in magento goes a long way

    • junaid says:

      Hi Herve,
      I agree that architecture does go a long way, but the very point of this post is to do with cases where architecture is secondary. Direct queries written this way are in fact as safe as using the core code, because the core code uses it in this way anyway!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.