使用plphp-如何将填充的十六进制输出写入PostgreSQL中的bytea列 [英] Using plphp - how to write padded hex output to a bytea column in PostgreSQL

问题描述

尝试将12个字节的字符串写入PostgreSQL

TRYING TO WRITE A 12-byte string to PostgreSQL

我们的代码计算出一个$ number。然后，我们不希望将其转换为十六进制，将其左侧填充零，然后将其写入PostgreSQL bytea字段。容易吧？

Our code calculates a $number. We't then like to convert it to hex, pad it left with zeros and write it into a PostgreSQL bytea field. Easy, right?

（例如）想要返回：\x000000002257（'（x'+ 12字节）
，即左填充数字8791的十六进制表示形式：

(As an example) Would like it to return: \x000000002257 ('\x' + 12 bytes) ie, the left-padded HEX representation of the number 8791:

$number = 8791;

$hexnumber = str_pad(dechex($number), 12, '0', STR_PAD_LEFT);
$packed_hex = pack('h*', $hexnumber);

// BOTH of below produce:  000000002257
pg_raise('notice', "hexnumber:         ".$hexnumber);

无法获取这些查询的任何来更新bytea希望。

Cannot get any of these queries to update the bytea as I'd wish. Help!

//$query = ("UPDATE blobtest SET destfile = '".$hexnumber."' WHERE pkey = ".$args[0]);

// $query = ("UPDATE blobtest SET destfile = '000000002257' WHERE pkey = ".$args[0]);
// Above produces:  \x303030303030303032323537
// (makes sense; it's quoted as a string)

// $query = ("UPDATE blobtest SET destfile = 000000002257 WHERE pkey = ".$args[0]);
// Above produces: ERROR:  column "destfile" is of type bytea but expression is of type integer

// $query = ("UPDATE blobtest SET destfile = '8791' WHERE pkey = ".$args[0]);
// Above produces:  \x38373931  as expected...

/ $query = ("UPDATE blobtest SET destfile = 8791 WHERE pkey = ".$args[0]);
// Above produces: ERROR:  column "destfile" is of type bytea but expression is of type integer

// $query = ("UPDATE blobtest SET destfile = '"."'".$packed_hex."'"."' WHERE pkey = ".$args[0]);
// Above produces:  \x only...

$query = ("UPDATE blobtest SET destfile = "."'".$packed_hex."'"." WHERE pkey = ".$args[0]);
// unterminated quoted string at or near "'"

推荐答案

您似乎已经忘记了BYtea字面量的前 \x 。如果 $ packed_hex 包含 000000002257 您可以输入：

It looks like you've just forgotten the leading \x for a bytea literal. If $packed_hex contains 000000002257 you could write:

$query = ("UPDATE blobtest SET destfile = '\x".$packed_hex."' WHERE pkey = ".$args[0]);

您将需要 SET bytea_output ='hex'在PostgreSQL 9.0及更低版本（IIRC）上以十六进制形式而不是棘手的旧八进制转义格式取回bytea。较新版本默认为 hex 。

You'll need to SET bytea_output = 'hex' on PostgreSQL 9.0 and below (IIRC) to get the bytea back out in hex form not the icky old octal escape format. Newer versions default to hex.

< soapbox> 您的代码显示了不良习惯。使用参数化查询来避免SQL注入。 packed_hex 现在可能会在您的应用中生成，但是谁知道以后如何重用此代码。始终使用参数化查询来避免 SQL注入。请参见有关SQL注入的PHP手册。< / soapbox>

<soapbox> Your code shows a bad habit. Use parameterised queries to avoid SQL injection. packed_hex might be generated in your app for now, but who knows how this code could get reused later. Always use parameterised queries to avoid SQL injection. See the PHP manual on SQL injection.</soapbox>

如所写，您的代码非常糟糕，非常不安全。想象一下，如果 $ args [0] 包含 NULL）； DROP SCHEMA public;-来自恶意用户。您刚发送：

As written, your code is woefully, horribly insecure. Imagine if $args[0] contained NULL);DROP SCHEMA public;-- from a malicious user. You just sent:

UPDATE blobtest SET destfile = '\000000002257' WHERE pkey = 0);DROP SCHEMA public;--);

到您的数据库，该数据库执行的是什么都不做的UPDATE，然后是 DROP SCHEMA public; 最有可能破坏了您的数据库，但随后的注释忽略了其余部分。糟糕，发现您的数据库出现了，鲍比表再次弹出。

to your database, which did an UPDATE that did nothing, followed by a DROP SCHEMA public; that most likely destroyed your database, then a comment that ignores the rest. Whoops, splat, there goes your database, bobby tables strikes again.

最好写成这样：

$stm = pg_prepare($connection, "", "UPDATE blobtest SET destfile = $1 WHERE pkey = $2");
$result = pg_execute($connection, "", array($packed_hex, $args[0]));

这篇关于使用plphp-如何将填充的十六进制输出写入PostgreSQL中的bytea列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！