0

TRYING TO WRITE A 12-byte string to PostgreSQL

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?

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

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 "'"
DrLou
  • 649
  • 5
  • 21

1 Answers1

1

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

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.

SQL Injection

<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>

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

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.

This would be better written as:

$stm = pg_prepare($connection, "", "UPDATE blobtest SET destfile = $1 WHERE pkey = $2");
$result = pg_execute($connection, "", array($packed_hex, $args[0]));
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778