Stored procedures - sql injection prevention and returning result as a single array

Question

Stored procedures - sql injection prevention and returning result as a single array

drc83 Mar '15

Created Mar '15	Last Reply Mar '15	Replies 1	Views 798	Votes 0

drc83 5.7k

Mar '15

Hi,

I have the following model:

class Robot extends \Phalcon\Mvc\Model{
    public static function getByName($name = '') {
        $sql = "CALL sp_getByName('$name');";
        $robot = new Robot();
        return new Resultset(null, $robot, $robot->getReadConnection()->query($sql));
    }
}

and called in the controller:

$name = "Johnny No. 5";
$robot = Robot::getByName($name)->toArray();
var_dump($robot);

This function calls a stored procedure from mysql. Using it this way does it automatically prevent sql injections or do I need to bind the values? (if so would you be able to provide an example).

Secondly the results are being returned as follows:

array(1) {
   [0]=>
   array(2) {
     ["name"]=>
     string(12) "Johnny No. 5"
     ["age"]=>
     string(1) "6"
   }
 }

Is there an easy way for them to be returned as:

   array(2) {
     ["name"]=>
     string(12) "Johnny No. 5"
     ["age"]=>
     string(1) "6"
   }

This specific stored procdure only return 1 row of results, so I don't need the results to be retuned as an array of an array it would just makes things cleaner to work with.

Thanks

Óscar Enríquez · Answer 1 · 2015-03-12T00:00:53-07:00

You need to bind user input.

This $robot->getReadConnection()->query($sql); is equivalent to:

<?php

# ...
$stmt = $pdo->prepare($sqlStatement);
$stmt->execute();
# ...

Use:

class Robot extends \Phalcon\Mvc\Model {
    public static function getByName($name = '') {
        $sql = "CALL sp_getByName(?);";
        $robot = new Robot();
        return new Resultset(null, $robot, $robot->getReadConnection()->query($sql, [$name]));
    }
}

About getting a single row you can do $robot = Robot::getByName($name)->getFirst();

And please, format code blocks https://forum.phalcon.io/help/markdown :)

drc83 · Answer 2 · 2015-03-12T03:53:41-07:00

Hi,

Thanks for the quick response. I tried using the code example

If I use:

$robot = Robot::getByName($name)->getFirst();

I get the following error:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

But using:

$robot = Robot::getTest($name)->toArray();

returns the results as an array or an array.

array(1) { [0]=> array(1) { ["name"]=> string(4) "Wall-E" } }

Thanks

Ps. sorry for not placing previous code in block - now been updated