CODEWITHSUNDEEP
phpmysqlpdolaravellaravel-4
Dwight
Dwight

Reputation: 12460

Escape raw SQL queries in Laravel 4

How does one go about escaping parameters passed to a raw query in Laravel 4? I expected something like DB::escape() (which rings a bell from Laravel 3) and also attempted DB::quote() (which I thought could be available through the PDO object)

$query = DB::select("SELECT * FROM users WHERE users.id = " . DB::escape($userId));

We can't use the select method with placeholders as the above is just a simplified example of what we are trying to achieve. We have a large custom query with a few nested select queries that cannot be adapted to the query builder.

What is the best approach to escaping something prior to inserting in Laravel 4?

EDIT:

I've just discovered that you can access the PDO object and use the quote function on it this way. Is this still the best approach, or is there an easier way to access this function?

DB::connection()->getPdo()->quote("string to quote");

Upvotes: 38

Views: 57227

Answers (8)

Bento
Bento

Reputation: 37

PHP Heredoc

<?php

use Illuminate\Support\Facades\DB;

$sql = <<<SQL
  WITH table1 AS(SELECT...
SQL;

$parameters = [1,2,3,...]

$table = DB::select($sql, $parameters);

Upvotes: 0

Sonny
Sonny

Reputation: 8326

Two answers here, that I use, have less verbose solutions built into the DB facade.

First, value quoting:

// From linked answer
DB::connection()->getPdo()->quote("string to quote");
// In the DB facade
DB::getPdo()->quote('string to quote');

Second, identifier quoting (table and column names):

// From linked answer
DB::table('x')->getGrammar()->wrap('table.column');
// In the DB facade
DB::getQueryGrammar()->wrap('table.column');

Upvotes: 9

mpen
mpen

Reputation: 282885

Here's a fuller example, showing how to escape both values and columns and extend Laravel's querybuilder:

<?php

namespace App\Providers;

use Illuminate\Database\Query\Builder;
use Illuminate\Support\ServiceProvider;


class DatabaseQueryBuilderMacroProvider extends ServiceProvider {

    public function register() {
        Builder::macro('whereInSet', function($columnName, $value) {
            /** @var \Illuminate\Database\Query\Grammars\Grammar $grammar */
            $grammar = $this->getGrammar();
            return $this->whereRaw('FIND_IN_SET(?,' . $grammar->wrap($columnName) . ')', [$value]);
        });
    }
}

Upvotes: 0

Rafał G.
Rafał G.

Reputation: 1682

I found this question when looking for generic sql escaping in Laravel. What I actually needed though was table/column name escaping. So, for future reference:

/**
 * Quotes database identifier, e.g. table name or column name. 
 * For instance:
 * tablename -> `tablename`
 * @param  string $field 
 * @return string      
 */
function db_quote_identifier($field) {
  static $grammar = false;
  if (!$grammar) {
    $grammar = DB::table('x')->getGrammar(); // The table name doesn't matter.
  }
  return $grammar->wrap($field);
}

Upvotes: 5

J. Bruni
J. Bruni

Reputation: 20492

I'm using this in my helpers.php at Laravel 5:

if ( ! function_exists('esc_sql'))
{
    function esc_sql($string)
    {
        return app('db')->getPdo()->quote($string);
    }
}

Then I can use esc_sql function where I need to pergorm escaping for raw SQL queries.

Upvotes: 4

Dwight
Dwight

Reputation: 12460

You can quote your strings this way, through the DB facade.

DB::connection()->getPdo()->quote("string to quote");

I did put this answer in my question when I discovered it, however I've now put it in as an actual answer to make it easier for others to find.

Upvotes: 66

The Alpha
The Alpha

Reputation: 146191

You may also try this, (Read Documentation)

$results = DB::select('SELECT * FROM users WHERE users.id = ?', array($userId));

Upvotes: 12

Arun Kumar M
Arun Kumar M

Reputation: 848

$value = Input::get("userID");

$results = DB::select( DB::raw("SELECT * FROM users WHERE users.id = :value"), array(
   'value' => $value,
 ));

More Details HERE

Upvotes: 23

Related Questions