Framework: Illuminate doesn't support binary querying in PostgreSQL

[jaw-sh]

The Illuminate Database is missing a feature that prevents me from being able to read binary data to the database with PostgreSQL. This is a critical missing feature that is blocking me from being able to support my users and their database choice.

Use case

I have a table called bans that has has a ban_ip_start and ban_ip_end column.

173.245.49.0/24
173.245.49.0 - 173.245.49.255
ad f5 35 ff - ad f5 35 00

By inserting this information as binary, I can easily do a query comparing the request IP to these ranges. "ban_ip_start" <= client ip binary <= "ban_ip_end".

Error

When selecting from my bans table (Only with PostgreSQL!), I get this error.

SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xc0a8 (SQL: select * from "bans" where ("ban_ip_start" <= ��
 and "ban_ip_end" >= ��
) and ("expires_at" > 2015-11-07 14:51:28 or "seen" = 0) and (select count(*) from "ban_appeals" where "ban_appeals"."ban_id" = "bans"."ban_id" and "approved" = 1) < 1 order by "board_uri" desc)

By reading the documentation, I thought that perhaps if I created my Postgres database with the SQL_ASCII character set, it wouldn't do any validation, which is a hack to get around this bug. Unfortunately, this led to even more breakage in my Laravel application; model attributes started turning into stream resources sometimes, and failing with this errors like this at times.

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bytea (SQL: insert into "captcha" ("client_ip", "solution", "hash", "created_at") values , VmG, �M��fany�W3xO˭�ҏ\, 2015-11-07 07:59:46) returning "captcha_id")

Possible Solution

This problem has been around since Laravel 4 and an entire library exists to deal with it. This does not work with Laravel 5.

https://github.com/ooxif/laravel-query-param

The PostgresSQL implementation of Eloquent needs some way of knowing if it should pass in PDO::PARAM_LOB instead of PDO::PARAM_STR. I'm not sure the specifics, but I think this should be the case with any binary item.

[jaw-sh]

As an addendum, I'd like to say that navigating the code for the Illuminate\Database is very frustrating. I'm trying to figure out how the code interacts with the PDO and I cannot unravel it. I'm very certain that PDO is used in the codebase, but I cannot pinpoint where the attribute / PDO relation is.

I'd just use the library I linked (as it is up to date with Laravel 5.1), but the solution involves wrapping this data in a helper function every single time it is referenced and I am simply unhappy with that.

[jaw-sh]

Okay, so I have found a solution to this. The answer is pg_escape_bytea and pg_unescape_bytea.

I have built these helper method.

if (!function_exists('is_binary'))
{
    function is_binary($input)
    {
        if (is_null($input))
        {
            return false;
        }

        if (is_integer($input))
        {
            return false;
        }

        return !ctype_print($input);
    }
}

if (!function_exists('binary_sql'))
{
    function binary_sql($bin)
    {
        if (DB::connection() instanceof \Illuminate\Database\PostgresConnection)
        {
            return pg_escape_bytea($bin);
        }

        return $bin;
    }
}

And then in my affected class, I have this.

    /**
     * Create a new model instance that is existing.
     *
     * @param  array  $attributes
     * @param  \Illuminate\Database\Connection|null  $connection
     * @return \Illuminate\Database\Eloquent\Model|static
     */
    public function newFromBuilder($attributes = array(), $connection = NULL)
    {
        if (isset($attributes->author_ip) && $attributes->author_ip !== null)
        {
            $attributes->author_ip = new IP($attributes->author_ip);
        }


        return parent::newFromBuilder($attributes);
    }

The IP helper class does the following:

if(is_resource($cidr))
{
    $cidr = stream_get_contents($cidr);
}

This is because the return from PostgreSQL for binary if correctly stored will be a "stream resource". If it is, we know to get the stream contents.

Now we check to see if its binary, and if it is, we can do whatever we want with it.

if (is_binary($cidr))
{
    $start = inet_ntop($cidr);
}

This will turn a binary representation of an IP to a 192.168.1.1 format. If dealing with non-IP data (like a SHA1 hash), you pg_unencode_bytea it.

    /**
     * Handles binary data for database connections.
     *
     * @param  binary  $bin
     * @return binary
     */
    private static function unescapeBinary($bin)
    {
        if (is_resource($bin))
        {
            $bin = stream_get_contents($bin);
        }

        if (DB::connection() instanceof \Illuminate\Database\PostgresConnection)
        {
            $bin = pg_unescape_bytea($bin);
        }

        return $bin;
    }

And there you have it. Postgres binary support in Laravel without weird hacks.

[jaw-sh]

@taylorotwell If I were to update the typecasting columns for Laravel's models so that you could specify IP or Binary and accomplish what I've done without hacks, would you merge it?

[jaw-sh]

I've reduced my problem to the smallest possible fix, but it involves adding 4 lines of code to Laravel's database engine for binary data to be manageable in PostreSQL.

When you insert information into PostgreSQL as a binary, you need to pg_escape_bytea it. So I have a helper function that does exactly that in this SET mutator.

/**
 * Sets our option value and encodes it if required.
 *
 * @param  mixed  $value
 * @return mixed
 */
public function setOptionValueAttribute($value)
{
    $this->attributes['option_value'] = binary_sql($value);
}

This way, when Laravel goes to insert, the value for that attribute is the binary string it needs to insert escaped properly. But, that information is useless to the application unless it's unescaped, so my GET mutator looks like this.

/**
 * Gets our option value and unwraps it from any stream wrappers.
 *
 * @param  mixed  $value
 * @return mixed
 */
public function getOptionValueAttribute($value)
{
    return binary_unsql($value);
}

Additionally, and this is important, the data returned by the PDO and Laravel when selecting binary information from PostreSQL is a stream resource. In my bianry_unsql helper, I have this.

function binary_unsql($bin)
{
    if (is_resource($bin))
    {
        $bin = stream_get_contents($bin);
    }

    if (DB::connection() instanceof \Illuminate\Database\PostgresConnection)
    {
        return pg_unescape_bytea($bin);
    }

    return $bin;
}

And this works great! The stream resource is converted to an escaped binary sequence and then unescaped if necessary. A get and set instance like this works fine.

$option->option_value +=5;
$option->save();

But, the issue is that when you are caching this information the stream resource breaks.

$option = Cache::remember('my_option', 60, function() {
    return Option::getMyOption();
});

echo $option->option_value;

The first time this runs, this works. But, the Cache driver will convert the stream resource into a integer 0 that cannot be unwound into data. Subsequent work with this cache fails.

My solution to this would be to make the PostgreSQL database driver to assume the contents of the attributes are the stream_get_contents if the value returned by the PDO is_resource. I cannot imagine a single situation where someone would want the stream resource and it needlessly complicates Eloquent-level work with unwrapping and wrapping.

[GrahamCampbell]

Closing since the PR was rejected. Sorry about that. I suspect it was due to overloading Taylor with text. Perhapse try writing a very concise version.

[jaw-sh]

@GrahamCampbell Well I'm sorry I couldn't reduce my detailed explanation about Laravel's multiple failures with Postgres to one sentence for Mr. President. Here's another failure that I need help with but I can assume I can't rely on you fucking people to this either.

See this?
Query 1: Much fast, 3ms.
Query 2: Very slow, replies, 600ms.
Query 3: Very fast, replies, 11ms, only difference is grammar for selecting by ID.

So in real simple short fucking words:
How do I change Postgres grammar to get 3 instead of 2?

[GrahamCampbell]

Of the top of my head, no idea. I'd suggest a quick fix would be to do a raw query.

Can I just point out that we're not here to give free advice on application specifics, especially if you ask the question by writing:

I can't rely on you fucking people to this either.

[jaw-sh]

I was told that Laravel supported Postgres and it does not. In my attempts to communicate with you about the problems I've experienced using Postgres on Laravel I have been cold shouldered and my issues have been closed for being off-topic. I've wasted my time and the time of ten thousand people trying to get Laravel working on Postgres in just the last week alone. My patience is wearing very thin and your documentation does not include any information about extending or modifying the Postgres grammar so that it actually works.

[GrahamCampbell]

Well, we're open to contributions to improve that.

[jaw-sh]

Great, well when I get this to work for myself I'll try and submit another pull request so that it can be closed and ignored for three months again. Thanks mate.

[GrahamCampbell]

We tend not to ignore actual pull requests. However, our issue system is primary there for the community to fix the issues, not the "core team".

[p5ych0]

frankly speaking it's just a case for improving bindValues method of PostgresConnection class like this

    public function bindValues($statement, $bindings)
    {
        foreach ($bindings as $key => $value) {
            $statement->bindValue(
                is_string($key) ? $key : $key + 1,
                $value,
                is_int($value) || is_float($value) ?
                    PDO::PARAM_INT :
                    ($value === null || ctype_print($value) ? PDO::PARAM_STR : PDO::PARAM_LOB)
            );
        }
    }