{"action":"closed","issue":{"url":"https://api.github.com/repos/laravel/framework/issues/10847","labels_url":"https://api.github.com/repos/laravel/framework/issues/10847/labels{/name}","comments_url":"https://api.github.com/repos/laravel/framework/issues/10847/comments","events_url":"https://api.github.com/repos/laravel/framework/issues/10847/events","html_url":"https://github.com/laravel/framework/issues/10847","id":115671133,"number":10847,"title":"Illuminate doesn't support binary querying in PostgreSQL","user":{"login":"jaw-sh","id":9262991,"avatar_url":"https://avatars.githubusercontent.com/u/9262991?v=3","gravatar_id":"","url":"https://api.github.com/users/jaw-sh","html_url":"https://github.com/jaw-sh","followers_url":"https://api.github.com/users/jaw-sh/followers","following_url":"https://api.github.com/users/jaw-sh/following{/other_user}","gists_url":"https://api.github.com/users/jaw-sh/gists{/gist_id}","starred_url":"https://api.github.com/users/jaw-sh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jaw-sh/subscriptions","organizations_url":"https://api.github.com/users/jaw-sh/orgs","repos_url":"https://api.github.com/users/jaw-sh/repos","events_url":"https://api.github.com/users/jaw-sh/events{/privacy}","received_events_url":"https://api.github.com/users/jaw-sh/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2015-11-07T14:54:54Z","updated_at":"2015-12-19T22:51:31Z","closed_at":"2015-12-19T22:51:31Z","body":"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.\r\n\r\n## Use case\r\nI have a table called `bans` that has has a `ban_ip_start` and `ban_ip_end` column.\r\n\r\n173.245.49.0/24\r\n173.245.49.0 - 173.245.49.255\r\nad f5 35 ff - ad f5 35 00\r\n\r\nBy 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\"`.\r\n\r\n## Error\r\nWhen selecting from my bans table (Only with PostgreSQL!), I get this error.\r\n\r\n```\r\nSQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xc0a8 (SQL: select * from \"bans\" where (\"ban_ip_start\" <= ��\u0001 and \"ban_ip_end\" >= ��\u0001) 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)\r\n```\r\n\r\nBy 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.\r\n\r\n```\r\nSQLSTATE[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\u001A˭�ҏ\\, 2015-11-07 07:59:46) returning \"captcha_id\")\r\n```\r\n\r\n## Possible Solution\r\nThis problem has been around since Laravel 4 and an entire library exists to deal with it. This does not work with Laravel 5.\r\n\r\nhttps://github.com/ooxif/laravel-query-param\r\n\r\nThe 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."}}