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

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

take a look at mysql_real_escape_string function.

Answer 2

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:

[UPDATED FOR SPECIFIC EXAMPLE]

$write = Mage::getSingleton("core/resource")->getConnection("core_write");
$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.

Hope that helps!

Thanks, Joe

---

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.