PHP/PostgreSQL:使用准备好的语句编写二进制数据 [英] PHP/PostgreSQL: writing binary data with prepared statements

问题描述

很长一段时间以来，我一直非常可靠地使用pg_prepare()和pg_execute()，但是由于我正在向其中写入二进制数据，所以遇到了麻烦.

我很惊讶地发现session_encode()在其字符串中返回NULL值，但总是被告知您不需要在准备好的语句中转义数据.那么，为什么当我向数据库写入5kb字段时，它在大约100个字节后会中断吗?

如果我执行:

UPDATE "solution_ubasket_temp" SET ("session_id", "session", "id") = ($1, $2, $3) WHERE "id"=$4"

并将输入数组的第二个成员设置为5kb返回值session_encode()，它将在找到第一个NULL值后切断.

select octet_length(session), * from solution_ubasket_temp

确认会话"中仅存储105个字节，即:

return_url | s:30:" https://solution-locale/Money "; complete | s :0:";购物篮| O:15:"SolutionUBasket":22:{s:13:

bin2hex()确认该字符串之后的下一个字节为NULL.

对text或bytea进行会话"没有区别.

我尝试使用Googling来查找"php postgresql预处理语句二进制"之类的内容，但是我刚收到有关MySQL和 http://www.postgresqltutorial.com/postgresql-php/postgresql-blob/，但这使用的是"PDO"，我不知道那是什么.

http://php.net/manual/en/function.pg -execute.php 没有此文本，但来自 http://php.net/manual/zh/function.pg-query-params.php :

不支持将用于teatea字段的值作为参数.改用pg_escape_bytea()或使用大对象函数.

可能会提示.也许我应该使用bytea而不是text并致电pg_escape_bytea().

我正在使用的桌子:

CREATE TABLE solution_ubasket_temp
(
  session_id character varying(40) NOT NULL,
  session text,
  last_update timestamp with time zone NOT NULL DEFAULT now(),
  order_id integer,
  status character varying(2),
  email character varying(100),
  id integer NOT NULL DEFAULT nextval('solution_ubasket_temp_id_seq1'::regclass),
  CONSTRAINT solution_ubasket_temp_id_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX solution_ubasket_temp_email_index
  ON solution_ubasket_temp
  USING btree
  (email COLLATE pg_catalog."default");

CREATE INDEX solution_ubasket_temp_session_id_index
  ON solution_ubasket_temp
  USING btree
  (session_id COLLATE pg_catalog."default");

我正在Debian 8.5和PostgreSQL 9.4上运行PHP 5.6.

解决方案

使用PDO

要有效地向表中插入/更新文本或二进制文件，您需要使用PDO，这就是代码的样子.

<?php
$dbh = new PDO('pgsql:host=localhost;dbname=test_rideshare', "postgres");

$fdata = file_get_contents('/tmp/sample.txt');
$n = 11;


$stmt = $dbh->prepare("INSERT INTO solution_ubasket_temp(session_id,session,order_id) Values(?,?,?) ");
$stmt->bindParam(1,$n);
$stmt->bindParam(2,$fdata, PDO::PARAM_LOB);
$stmt->bindParam(3,$n);


$stmt->execute();
print $stmt->debugDumpParams();
print_r($stmt->errorInfo());
print_r($dbh->errorInfo());


$stmt = $dbh->prepare("SELECT session FROM solution_ubasket_temp LIMIT 1");
$stmt->execute();
$a = $stmt->fetch();
print_r($a);

file_put_contents("/tmp/sample2.txt",$a['session']);

?>

上面的代码示例显示了如何进行插入，然后再次读取数据.它还会将相同的数据写入另一个文件以进行比较.视觉或差异比较将显示它们是相同的.

要进行更新，只需将insert语句替换为update语句

二进制数据

如果要存储二进制数据，则应使用二进制字段.那是

session bytea,

实际上，我从不使用文本字段，每当我需要存储大对象时，我都选择二进制文件，并且在可能的情况下，我都尽量避免在db中存储大对象.实际上，尽管我上面的代码演示了如何在数据库中存储文件.这是我在生产中永远不会做的事情.

PHP序列化与JSON

您的数据看起来像是序列化的PHP.我可以建议您改用JSON，然后将数据存储在 JSONB中字段?与PHP序列化和反序列化相比，这将为您提供更多选择，更不用说提及使用PHP序列化时几乎所有编程语言都支持JSON的事实了.

I've been using pg_prepare() and pg_execute() very reliably for a long time now, but now that I'm writing binary data to it, I've run into difficulties.

I was surprised to learn that session_encode() returns NULL values in its strings, but have always been told that you don't need to escape data in prepared statements. So why when I write a 5kb field to the db, does it cut off after about 100 bytes?

If I execute:

UPDATE "solution_ubasket_temp" SET ("session_id", "session", "id") = ($1, $2, $3) WHERE "id"=$4"

and set the 2nd member of the input array to the 5kb return value of session_encode(), it cuts off after the first NULL value it finds.

select octet_length(session), * from solution_ubasket_temp

confirms that there are only 105 bytes stored in "session" which are:

return_url|s:30:"https://solution-locale/Moneys";complete|s:0:"";basket|O:15:"SolutionUBasket":22:{s:13:

bin2hex() confirms that the next byte after that string is a NULL.

Making "session" a text or bytea makes no difference.

I tried Googling for things like "php postgresql prepared statements binary" but I just got a lot of hits about MySQL and http://www.postgresqltutorial.com/postgresql-php/postgresql-blob/ but that uses something called "PDO" and I don't know what that is.

http://php.net/manual/en/function.pg-execute.php doesn't have this text, but from http://php.net/manual/en/function.pg-query-params.php:

Values intended for bytea fields are not supported as parameters. Use pg_escape_bytea() instead, or use the large object functions.

might give a hint. Maybe I'm supposed to be using bytea instead of text and call pg_escape_bytea().

The table I'm working w/:

CREATE TABLE solution_ubasket_temp
(
  session_id character varying(40) NOT NULL,
  session text,
  last_update timestamp with time zone NOT NULL DEFAULT now(),
  order_id integer,
  status character varying(2),
  email character varying(100),
  id integer NOT NULL DEFAULT nextval('solution_ubasket_temp_id_seq1'::regclass),
  CONSTRAINT solution_ubasket_temp_id_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX solution_ubasket_temp_email_index
  ON solution_ubasket_temp
  USING btree
  (email COLLATE pg_catalog."default");

CREATE INDEX solution_ubasket_temp_session_id_index
  ON solution_ubasket_temp
  USING btree
  (session_id COLLATE pg_catalog."default");

I'm running PHP 5.6 on Debian 8.5 and PostgreSQL 9.4.

解决方案

Using PDO

To effectively insert/update text or binary to your table, you need to use PDO, this is what the code might look like.

<?php
$dbh = new PDO('pgsql:host=localhost;dbname=test_rideshare', "postgres");

$fdata = file_get_contents('/tmp/sample.txt');
$n = 11;


$stmt = $dbh->prepare("INSERT INTO solution_ubasket_temp(session_id,session,order_id) Values(?,?,?) ");
$stmt->bindParam(1,$n);
$stmt->bindParam(2,$fdata, PDO::PARAM_LOB);
$stmt->bindParam(3,$n);


$stmt->execute();
print $stmt->debugDumpParams();
print_r($stmt->errorInfo());
print_r($dbh->errorInfo());


$stmt = $dbh->prepare("SELECT session FROM solution_ubasket_temp LIMIT 1");
$stmt->execute();
$a = $stmt->fetch();
print_r($a);

file_put_contents("/tmp/sample2.txt",$a['session']);

?>

The above code sample shows how to do an insert and then read the data again. It also writes the same data to another file for comparison. Visual or diff comparison will show that they are the same.

For update, just replace the insert statement with an update statement

Binary Data

If you are storing binary data, you should use a binary field. That is

session bytea,

In fact, I never use text fields, when ever I have the need to store large objects I choose binary and whenever possible I try to avoid storing large objects in the db. In fact though my code above demonstrates how to store a file in the DB. this is something that I would never do in production.

PHP Serialize vs JSON

You data looks like it's serialized PHP. May I suggest that you switch to JSON instead and store the data in an JSONB field instead? That would give you many more options than PHP serialize and deserialize, not to mention that mention the fact that JSON is supported in almost any programming language while using PHP serialize would lock you down to PHP.

这篇关于PHP/PostgreSQL:使用准备好的语句编写二进制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！