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

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> only $19.95

1.3.4 Escaping Strings for Inclusion in SQL Commands

PQescapeLiteral

char *PQescapeLiteral(PGconn *conn, const char *str, 
  size_t length);

PQescapeLiteral escapes a string for use within an SQL command. This is useful when inserting data values as literal constants in SQL commands. Certain characters (such as quotes and backslashes) must be escaped to prevent them from being interpreted specially by the SQL parser. PQescapeLiteral performs this operation. PQescapeLiteral returns an escaped version of the str parameter in memory allocated with malloc(). This memory should be freed using PQfreemem() when the result is no longer needed. A terminating zero byte is not required, and should not be counted in length. (If a terminating zero byte is found before length bytes are processed, PQescapeLiteral stops at the zero; the behavior is thus rather like strncpy.) The return string has all special characters replaced so that they can be properly processed by the PostgreSQL string literal parser. A terminating zero byte is also added. The single quotes that must surround PostgreSQL string literals are included in the result string. On error, PQescapeLiteral returns NULL and a suitable message is stored in the conn object.

Tip: It is especially important to do proper escaping when handling strings that were received from an untrustworthy source. Otherwise there is a security risk: you are vulnerable to “SQL injection” attacks wherein unwanted SQL commands are fed to your database.

Note that it is not necessary nor correct to do escaping when a data value is passed as a separate parameter in PQexecParams or its sibling routines.

PQescapeIdentifier

char *PQescapeIdentifier(PGconn *conn, const char *str, 
  size_t length);

PQescapeIdentifier escapes a string for use as an SQL identifier, such as a table, column, or function name. This is useful when a user-supplied identifier might contain special characters that would otherwise not be interpreted as part of the identifier by the SQL parser, or when the identifier might contain upper case characters whose case should be preserved. PQescapeIdentifier returns a version of the str parameter escaped as an SQL identifier in memory allocated with malloc(). This memory must be freed using PQfreemem() when the result is no longer needed. A terminating zero byte is not required, and should not be counted in length. (If a terminating zero byte is found before length bytes are processed, PQescapeIdentifier stops at the zero; the behavior is thus rather like strncpy.) The return string has all special characters replaced so that it will be properly processed as an SQL identifier. A terminating zero byte is also added. The return string will also be surrounded by double quotes. On error, PQescapeIdentifier returns NULL and a suitable message is stored in the conn object.

Tip: As with string literals, to prevent SQL injection attacks, SQL identifiers must be escaped when they are received from an untrustworthy source.

PQescapeStringConn

size_t PQescapeStringConn(PGconn *conn,
                          char *to, const char *from,
                          size_t length,
                          int *error);

PQescapeStringConn escapes string literals, much like PQescapeLiteral. Unlike PQescapeLiteral, the caller is responsible for providing an appropriately sized buffer. Furthermore, PQescapeStringConn does not generate the single quotes that must surround PostgreSQL string literals; they should be provided in the SQL command that the result is inserted into. The parameter from points to the first character of the string that is to be escaped, and the length parameter gives the number of bytes in this string. A terminating zero byte is not required, and should not be counted in length. (If a terminating zero byte is found before length bytes are processed, PQescapeStringConn stops at the zero; the behavior is thus rather like strncpy.) to shall point to a buffer that is able to hold at least one more byte than twice the value of length, otherwise the behavior is undefined. Behavior is likewise undefined if the to and from strings overlap. If the error parameter is not NULL, then *error is set to zero on success, nonzero on error. Presently the only possible error conditions involve invalid multibyte encoding in the source string. The output string is still generated on error, but it can be expected that the server will reject it as malformed. On error, a suitable message is stored in the conn object, whether or not error is NULL. PQescapeStringConn returns the number of bytes written to to, not including the terminating zero byte.

PQescapeString

size_t PQescapeString (char *to, const char *from, size_t 
  length);

PQescapeString is an older, deprecated version of PQescapeStringConn; the difference is that it does not take conn or error parameters. Because of this, it cannot adjust its behavior depending on the connection properties (such as character encoding) and therefore it might give the wrong results. Also, it has no way to report error conditions. PQescapeString 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 PQescapeStringConn.

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 1A: 6.4 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 should 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 might 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.