如何通过HDBC将二进制数据放入postgres

Question

我有一个Haskell应用程序，作为许多步骤之一，它需要在数据库中存储和检索原始二进制blob数据。我并没有完全放弃将数据存储在普通磁盘文件中的决定，但这确实会导致另一轮权限问题，所以现在我想使用数据库。

我创建了一个具有bytea类型列的表。

我的记忆中有一个懒惰的Bytestring。

当我打这样的电话时

run conn "INSERT INTO documents VALUES (?)" [toSql $ rawData mydoc]

postgres对这些数据有点生气。确切的错误消息是

invalid byte sequence for encoding "UTF8": 0xcf72

毫无疑问，我也知道我在数据流中有NUL值。那么，考虑到所有这些，对数据进行安全编码以供插入的正确方法是什么？

---

更新

这是我的表的描述

db=> d+ documents
                          Table "public.documents"
     Column      |            Type             | Modifiers | Storage  | Description 
-----------------+-----------------------------+-----------+----------+-------------
 id              | character varying(16)       | not null  | extended | 
 importtime      | timestamp without time zone | not null  | plain    | 
 filename        | character varying(255)      | not null  | extended | 
 data            | bytea                       | not null  | extended | 
 recordcount     | integer                     | not null  | plain    | 
 parsesuccessful | boolean                     | not null  | plain    | 
Indexes:
    "documents_pkey" PRIMARY KEY, btree (id)

这是一个模块的全文，它演示了我在添加jamsdidh的代码后遇到的当前问题。我的错误消息已从上面的编码问题更改为"类型byta的无效输入语法"。

module DBMTest where
import qualified Data.Time.Clock as Clock
import Database.HDBC.PostgreSQL
import Database.HDBC
import Data.ByteString.Internal
import Data.ByteString hiding (map)
import Data.Char
import Data.Word8
import Numeric
exampleData = pack ([0..65536] :: [Word8]) :: ByteString
safeEncode :: ByteString -> ByteString
safeEncode x = pack (convert' =<< unpack x)
    where
    convert' :: Word8 -> [Word8]
    convert' 92 = [92, 92]
    convert' x | x >= 32 && x < 128 = [x]
    convert' x = 92:map c2w (showIntAtBase 8 intToDigit x "")
runTest = do
    conn <- connectPostgreSQL "dbname=db"
    t <- Clock.getCurrentTime
    withTransaction conn
        (conn -> run conn
            "INSERT INTO documents (id, importTime, filename, data, recordCount, parseSuccessful) VALUES (?, ?, ?, ?, ?, ?)"
            [toSql (15 :: Int),
             toSql t,
             toSql ("Demonstration data" :: String),
             toSql $ safeEncode exampleData,
             toSql (15 :: Int),
             toSql (True :: Bool)])

Answer 1

我认为这是HDBC postgresql中的一个错误。我可以解释为什么我认为会这样，并可以给你一个我整理并测试的变通方法。

---

我希望HDBC-postgresql将字节串转换为要插入的适当格式，但您可以快速验证它是否希望字节串保存数据的八进制退格转义值。例如，

run conn "INSERT INTO documents VALUES (?)" [toSql $ B.pack [92, 0x31, 0x30, 0x31]]

将单个字符"A"插入数据库！只有当您意识到[92，0x31，0x30，0x31]是"\101"的ascii表示，而"\ 101"是"A"的八进制表示时，这才有意义。因为八进制退格转义字符串保证允许32-127范围内的值直接通过（有关详细信息，请参阅评论中提供的链接Richard Huxton），所以插入查询实际上对标准英语文本有效，可能会被忽视。。。。

run conn "INSERT INTO documents VALUES (?)" [toSql $ B.pack [65]]

还插入"A"。高于127的值不能保证有效，而是根据所使用的字符编码进行解释。如果您查看HDBCpostgresql代码或查询的日志，您可以看到它正在将变量"client_encoding"设置为utf8。因此，来自字节串的数据应该是有效的utf8，当它看到一个不能作为utf8字符存在的序列时，就会抱怨。

正确的修复方法是等待HDBC postgresql人员修复该错误，但与此同时，您可以使用此代码作为变通方法。。。。

import Data.ByteString.Internal
import Data.Char
import Data.Word8
import Numeric
import Text.Printf
convert::B.ByteString->B.ByteString
convert x = B.pack (convert' =<< B.unpack x)
          where
            convert'::Word8->[Word8]
            convert' 92 = [92, 92]
            convert' x | x >= 32 && x < 128 = [x]
            convert' x = 92:map c2w (printf "%03o" x) 

现在你只需要使用

run conn "INSERT INTO documents VALUES (?)" [toSql $ convert $ rawData mydoc]