Using Magento Methods to write Insert Queries with care for SQL Injection

Question

I am using the Magento's functionality to insert & update queries. My requirement is that I want to take care of SQL Injection, when doing these types of queries. But I'm unable to find how Magento does this. I'm providing one start sample. Please provide me with one complete example.

<?php
$write = Mage::getSingleton("core/resource")->getConnection("core_write");
$sql = "INSERT INTO Mage_Example (Name, Email, Company, Description, Status, Date)
    VALUES ('$name', '$email', '$company', '$desc', '0', NOW())";
?>

Now I want to change the above query to prevent the possible SQL Injection. I don't want to use the default "mysql_real_escape_string()" built-in function of PHP. Can anybody please provide me with one useful solution, using the "$write" DB Handler.

Any help is greatly appreciated.

Answer 1

Okay, researched this one a little bit. If you can get an instance of a DB_Adapter (which I believe that resource call will return), this shouldn't be too tough. Deep down inside, Magento is based on Zend Framework, and the DB adapter specifically is descended from Zend_Db_Adapter, so you can use those methods for free. See the link before for more examples, but here's the syntax provided in the docs, which should escape your input automagically:

$write = Mage::getSingleton("core/resource")->getConnection("core_write");

// Concatenated with . for readability
$query = "insert into mage_example "
       . "(name, email, company, description, status, date) values "
       . "(:name, :email, :company, :desc, 0, NOW())";

$binds = array(
    'name'    => "name' or 1=1",
    'email'   => "email",
    'company' => "company",
    'desc'    => "desc",
);
$write->query($query, $binds);

Again, see the docs for more information.

---

UPDATE:

I've changed the example above. The object that you get back with your core_write request is a PDO object that exposes a query method (see above) that will let you used parameterized queries. This is BY FAR a better approach than attempting to use something like mysql_real_escape_string for data sanitization, and I've tested the above code for correctness. Note that, in contrast to most MySQL parameterized queries, the binding is done with :labels, and also that you need no quotes for your vars.

In response to your other point, and as noted below, the "right" way to do it in Magento is not to use direct queries at all. The Magento object models are well development and meant to abstract this kind of implementation detail away from you, because you shouldn't need to concern yourself with it. To do it "correctly", create a new database-based model and save the headache.

Answer 2

I am using this for inserting multiple rows to the table

$table = Mage::getSingleton('core/resource')->getTableName('table_name');
$rows = array(
   array('cal_1'=>'value','cal_2'=>'value','cal_3'=>'value'),
   array('cal_1'=>'value','cal_2'=>'value','cal_3'=>'value')
);

public function insertRows($table,$rows)
{
   $write = Mage::getSingleton('core/resource')->getConnection('core_write');
   $write->insertMultiple($table,$rows);
}

Answer 3

In resource file.

public function saveToTable($param){

$table = $this->getMainTable(); 

$this->_getWriteAdapter->insert($table,array(
          'col_1'=>$param['data1']
          'col_2'=>$param['data2']
          'col_3'=>$param['data3']
      ));
}

returns number of rows affected.

Answer 4

i guess escaping the $name, $email and other variables will be enought.

take a look at mysql_real_escape_string function.