FuelPHP Forums

Peter

Hi, I prefer using DB::query and type my own MySQL query instead of using the DB::select function. But the only problem with using the DB::query function is that the variables are not escaped automatically. What is the best way to escape the variables? Off course I can use the escape function for every variable, but maybe there is an easier way? Something like CodeIgniter?

$query = "SELECT * FROM table WHERE first_name = ? AND last_name = ?";
$this->db->query($query, array("John", "Doe"));

Now John and Doe are escaped automatically.

Peter

Harro Verton wrote on Tuesday 12th of July 2011:
Not documented yet, but you might be able to do
$first = 'John';
$last = 'Doe';

$query = DB::query("SELECT * FROM table WHERE first_name = :first AND last_name = :last");
$query->bind(':first', $first)->bind(':last', $last)->execute();
Note that bind() works by reference, so if you change the value of $first between the bind() and the execute() call, this change is reflected in the query. If you don't want that, use param() instead, which works by value. Both bind() and param() will escape the values before placing it in the query.

Thanks WanWizard, this is the solution I was looking for!

Harro

Fuel does not promote writing hard-coded SQL statements. DB:query() is provided because sometimes you have to, but in general you should use the DB class methods to construct a query. It ensures everything is escaped, and allows the query builder to generate optimal and portable SQL. If you absolutely need it, create a feature request at http://github.com/fuel/core/issues. This isn't more complicated:

$result = \DB::select()->where('first_name', '=', 'John')->where('last_name', '=', 'Doe')->from('table')->execute();

nbst

There's DB::quote(). Carl

Harro

Not documented yet, but you might be able to do

$first = 'John';
$last = 'Doe';

$query = DB::query("SELECT * FROM table WHERE first_name = :first AND last_name = :last");
$query->bind(':first', $first)->bind(':last', $last)->execute();

Note that bind() works by reference, so if you change the value of $first between the bind() and the execute() call, this change is reflected in the query. If you don't want that, use param() instead, which works by value. Both bind() and param() will escape the values before placing it in the query.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion