PostgreSQL Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 408 pages ISBN 0954612035 RRP £19.95 ($34.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 2 Buy the book here! >>> support free documentation

1.3.5 Escaping Binary Strings for Inclusion in SQL Commands

PQescapeByteaConn

Escapes binary data for use within an SQL command with the type bytea. As with PQescapeStringConn, this is only used when inserting data directly into an SQL command string.

unsigned char *PQescapeByteaConn(PGconn *conn,
                                 const unsigned char *from,
                                 size_t from_length,
                                 size_t *to_length);

Certain byte values must be escaped (but all byte values can be escaped) when used as part of a bytea literal in an SQL statement. In general, to escape a byte, it is converted into the three digit octal number equal to the octet value, and preceded by usually two backslashes. The single quote (') and backslash (\) characters have special alternative escape sequences. See Volume 1: Binary Data Types for more information. PQescapeByteaConn performs this operation, escaping only the minimally required bytes. The from parameter points to the first byte of the string that is to be escaped, and the from_length parameter gives the number of bytes in this binary string. (A terminating zero byte is neither necessary nor counted.) The to_length parameter points to a variable that will hold the resultant escaped string length. This result string length includes the terminating zero byte of the result. PQescapeByteaConn returns an escaped version of the from parameter binary string in memory allocated with malloc(). This memory must be freed using PQfreemem() when the result is no longer needed. The return string has all special characters replaced so that they can be properly processed by the PostgreSQL string literal parser, and the bytea input function. A terminating zero byte is also added. The single quotes that must surround PostgreSQL string literals are not part of the result string. On error, a NULL pointer is returned, and a suitable error message is stored in the conn object. Currently, the only possible error is insufficient memory for the result string.

PQescapeBytea

PQescapeBytea is an older, deprecated version of PQescapeByteaConn.

unsigned char *PQescapeBytea(const unsigned char *from,
                             size_t from_length,
                             size_t *to_length);

The only difference from PQescapeByteaConn is that PQescapeBytea does not take a PGconn parameter. Because of this, it cannot adjust its behavior depending on the connection properties (in particular, whether standard-conforming strings are enabled) and therefore it may give the wrong results. Also, it has no way to return an error message on failure. PQescapeBytea can be used safely in single-threaded client programs that work with only one PostgreSQL connection at a time (in this case it can find out what it needs to know “behind the scenes”). In other contexts it is a security hazard and should be avoided in favor of PQescapeByteaConn.

PQunescapeBytea

Converts a string representation of binary data into binary data--the reverse of PQescapeBytea. This is needed when retrieving bytea data in text format, but not when retrieving it in binary format.

unsigned char *PQunescapeBytea(const unsigned char *from,
 size_t *to_length);

The from parameter points to a string such as might be returned by PQgetvalue when applied to a bytea column. PQunescapeBytea converts this string representation into its binary representation. It returns a pointer to a buffer allocated with malloc(), or null on error, and puts the size of the buffer in to_length. The result must be freed using PQfreemem when it is no longer needed. This conversion is not exactly the inverse of PQescapeBytea, because the string is not expected to be “escaped” when received from PQgetvalue. In particular this means there is no need for string quoting considerations, and so no need for a PGconn parameter.

PQfreemem

Frees memory allocated by libpq.

void PQfreemem(void *ptr);

Frees memory allocated by libpq, particularly PQescapeByteaConn, PQescapeBytea, PQunescapeBytea, and PQnotifies. It is needed by Microsoft Windows, which cannot free memory across DLLs, unless multithreaded DLLs (/MD in VC6) are used. On other platforms, this function is the same as the standard library function free().