Escape raw SQL queries in Laravel 4

Question

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");

Thanks. +1 for including the solution (you could also have posted it separatedly as an answer, and accepted it... could save some time for us) — J. Bruni, Apr 07 '14 at 00:05
@J.Bruni, good point! I've now added it as an actual answer as well so it should be easier to find. — Dwight, Apr 22 '14 at 00:36

score 64 · Accepted Answer · answered Apr 22 '14 at 00:35

64

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.

answered Apr 22 '14 at 00:35

Dwight

12,120
6
51
64

4

Thanks! It's amazing how people are adamant about answering the wrong question. I need the same answer as you did, and parameters do not work in my situation either. – Gabriel Magana Apr 28 '14 at 23:16
1

Works, but such a long thing to call! DB::escape() would be nice. – Andrew Mar 10 '18 at 03:19
@Andrew Sums up frameworks functionality – Martin Zvarík Jul 07 '23 at 23:21

score 23 · Answer 2 · answered Sep 23 '13 at 02:49

23

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

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

More Details HERE

answered Sep 23 '13 at 02:49

Arun Kumar M

848
9
14

4

This does not answer the question, it stated that I was not looking for the solution with placeholders but rather the appropriate way to escape one variable. – Dwight Sep 24 '13 at 01:24
1

@Dwight: As is moot. As you escape to actually encode the variable value as SQL, so does the placeholder. So does PDO. – hakre May 11 '14 at 08:49
1

beware, I have had problems using this combined with more `where` statements. The query builder does not combine the placeholders in the `select()` and the other dynamic replacements like in the `where`. – tomvo Jul 22 '15 at 15:22
@hakre What if I need to escape a bunch (a variable number) of variables to put into an "IN ()" part of a statement? – Jānis Elmeris Dec 04 '16 at 17:00
1

@Janis: You find such questions answered in [Can I bind an array to an IN() condition?](http://stackoverflow.com/q/920353/367456) and it's sister Q&A. – hakre Dec 05 '16 at 11:29

score 11 · Answer 3 · answered Sep 23 '13 at 02:54

11

You may also try this, (Read Documentation)

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

answered Sep 23 '13 at 02:54

The Alpha

143,660
29
287
307

2

+1 to this answer. Using array bindings, like the example above, will protect you. Further info here http://forums.laravel.io/viewtopic.php?id=11068 – Laurence Sep 23 '13 at 03:03
1

This does not answer the question, it stated that I was not looking for the solution with placeholders but rather the appropriate way to escape one variable. – Dwight Sep 24 '13 at 01:22
1

@Dwight, This is what the `array binding/bound params` does, safe, escaped, no sql injections could be applied and not only place holders. Just read [this answer](http://stackoverflow.com/questions/3716373/real-escape-string-and-pdo) to clear yourself. – The Alpha Sep 24 '13 at 01:35
@Dwight, also notice the first comment, didn't it clarify you ? – The Alpha Sep 24 '13 at 01:39

score 7 · Answer 4 · answered Jun 26 '19 at 20:59

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');

score 5 · Answer 5 · answered Jul 27 '15 at 13:36

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);
}

`DB::query()->getGrammar()` might be a more straightforward approach. Keeps other devs from asking too many questions. — amphetamachine, Jun 18 '19 at 15:48

score 4 · Answer 6 · answered Jun 08 '15 at 20:41

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.

score 0 · Answer 7 · answered Nov 03 '17 at 22:25

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]);
        });
    }
}

score 0 · Answer 8 · answered Mar 19 '22 at 19:14

0

PHP Heredoc

<?php

use Illuminate\Support\Facades\DB;

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

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

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

answered Mar 19 '22 at 19:14

Bento

37
1
6

Escape raw SQL queries in Laravel 4

8 Answers8

Linked