118
Chapter 31. libpq - C Library
Tip: It is especially important todoproper escapingwhenhandlingstringsthat werereceived
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-suppliedidentifier 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 identi-
fier. 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, toprevent SQLinjectionattacks, 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
.
683
116
Chapter 31. libpq - C Library
PQescapeStringConn
returns the number of bytes written to
to
,notincluding the terminating
zero byte.
PQescapeString
PQescapeString
is an older, deprecated version of
PQescapeStringConn
.
size_t PQescapeString (char
*
to, const char
*
from, size_t length);
The only difference from
PQescapeStringConn
is that
PQescapeString
does not take
PGconn
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 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 securityhazard 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 when used as part of a
bytea
literal in an SQL statement.
PQescapeByteaConn
escapes bytes using either hexencodingor backslash escaping. See Sec-
tion 8.4 for more information.
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 mem-
ory 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,
PQescapeBytea
can only be used safely in client
programs that use a single PostgreSQL connection at a time (in this case it can find out what it
needs to know “behind the scenes”). It might give the wrong results if used in programs that use
multiple database connections (use
PQescapeByteaConn
in such cases).
684
118
Chapter 31. libpq - C Library
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 rep-
resentation. 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 stringis not expected
to be “escaped” when received from
PQgetvalue
.In particular this means there is no need for
string quotingconsiderations, and sono needfor a
PGconn
parameter.
31.4. Asynchronous Command Processing
The
PQexec
function is adequate for submitting commands in normal, synchronous applications. It
has a few deficiencies, however, that can be of importance to some users:
•
PQexec
waits for thecommandtobe completed. Theapplicationmighthaveother worktodo(such
as maintaining a user interface), in which case it won’t want to block waiting for the response.
•
Since the execution of the client application is suspended while it waits for the result, it is hard for
the application to decide that it would like to try to cancel the ongoing command. (It can be done
from a signal handler, but not otherwise.)
•
PQexec
can return only one
PGresult
structure. If the submitted command string contains multi-
ple SQL commands, all but the last
PGresult
are discarded by
PQexec
.
•
PQexec
always collects the command’s entire result, buffering it in a single
PGresult
.While this
simplifies error-handling logic for the application, itcan be impracticalfor results containing many
rows.
Applications that do not like these limitations can instead use the underlying functions that
PQexec
is built from:
PQsendQuery
and
PQgetResult
.There are also
PQsendQueryParams
,
PQsendPrepare
,
PQsendQueryPrepared
,
PQsendDescribePrepared
,
and
PQsendDescribePortal
, which can be used with
PQgetResult
to duplicate the
functionality of
PQexecParams
,
PQprepare
,
PQexecPrepared
,
PQdescribePrepared
, and
PQdescribePortal
respectively.
PQsendQuery
Submits a command to the server without waitingfor the result(s). 1 is returned if the command
was successfully dispatched and 0 if not (in which case, use
PQerrorMessage
to get more
information about the failure).
int PQsendQuery(PGconn
*
conn, const char
*
command);
685
120
Chapter 31. libpq - C Library
After successfully calling
PQsendQuery
,call
PQgetResult
one or more times to obtain the
results.
PQsendQuery
cannot be called again (on the same connection) until
PQgetResult
has
returned a null pointer, indicating that the commandis done.
PQsendQueryParams
Submits a command and separate parameters to the server without waiting for the result(s).
int PQsendQueryParams(PGconn
*
conn,
const char
*
command,
int nParams,
const Oid
*
paramTypes,
const char
*
const
*
paramValues,
const int
*
paramLengths,
const int
*
paramFormats,
int resultFormat);
This is equivalent to
PQsendQuery
except that query parameters can be specified separately
from thequerystring. Thefunction’s parameters are handled identically to
PQexecParams
.Like
PQexecParams
,it will not work on 2.0-protocol connections, and it allows only one command
in the query string.
PQsendPrepare
Sends a request to create a prepared statement with the given parameters, without waiting for
completion.
int PQsendPrepare(PGconn
*
conn,
const char
*
stmtName,
const char
*
query,
int nParams,
const Oid
*
paramTypes);
This is an asynchronous version of
PQprepare
:it returns 1 if it was able to dispatch the re-
quest, and 0 if not. After a successful call, call
PQgetResult
to determine whether the server
successfully created the prepared statement. The function’s parameters are handled identically
to
PQprepare
.Like
PQprepare
,it will not work on 2.0-protocol connections.
PQsendQueryPrepared
Sends a request to execute a prepared statement with given parameters, without waiting for the
result(s).
int PQsendQueryPrepared(PGconn
*
conn,
const char
*
stmtName,
int nParams,
const char
*
const
*
paramValues,
const int
*
paramLengths,
const int
*
paramFormats,
int resultFormat);
This is similar to
PQsendQueryParams
,but the commandtobe executedis specifiedbynaming
apreviously-prepared statement, instead of giving a query string. The function’s parameters
are handled identically to
PQexecPrepared
.Like
PQexecPrepared
,it will not work on 2.0-
protocol connections.
PQsendDescribePrepared
Submits a request to obtain information about the specified prepared statement, without waiting
for completion.
int PQsendDescribePrepared(PGconn
*
conn, const char
*
stmtName);
This is an asynchronous version of
PQdescribePrepared
: it returns 1 if it was able to
dispatch the request, and 0 if not. After a successful call, call
PQgetResult
to obtain the
686
109
Chapter 31. libpq - C Library
results. The function’s parameters are handled identically to
PQdescribePrepared
. Like
PQdescribePrepared
,it will not work on 2.0-protocol connections.
PQsendDescribePortal
Submits a request to obtain information about the specified portal, without waiting for comple-
tion.
int PQsendDescribePortal(PGconn
*
conn, const char
*
portalName);
This is anasynchronous versionof
PQdescribePortal
:itreturns 1if itwas able todispatchthe
request, and 0 if not. After a successful call, call
PQgetResult
to obtain the results. The func-
tion’s parameters are handled identically to
PQdescribePortal
.Like
PQdescribePortal
,it
will not work on 2.0-protocol connections.
PQgetResult
Waits for the next result from a prior
PQsendQuery
,
PQsendQueryParams
,
PQsendPrepare
,
PQsendQueryPrepared
,
PQsendDescribePrepared
, or
PQsendDescribePortal
call,
and returns it. A null pointer is returned when the command is complete and there will be no
more results.
PGresult
*
PQgetResult(PGconn
*
conn);
PQgetResult
must be called repeatedly until it returns a null pointer, indicating that the com-
mand is done. (If called when no command is active,
PQgetResult
will just return a null
pointer at once.) Each non-null result from
PQgetResult
should be processed using the same
PGresult
accessor functions previously described. Don’t forget to free each result object with
PQclear
when done with it. Note that
PQgetResult
will block only if a command is active
andthe necessary response data has not yet been read by
PQconsumeInput
.
Note: Even when
PQresultStatus
indicates a fatal error,
PQgetResult
should be called
until it returns a null pointer, to allow libpq to process the error information completely.
Using
PQsendQuery
and
PQgetResult
solves one of
PQexec
’s problems: If a command string
contains multiple SQL commands, the results of those commands can be obtainedindividually. (This
allows a simple form of overlapped processing, by the way: the client can be handling the results of
one command while the server is still workingon later queries in the same command string.)
Another frequently-desired feature that can be obtained with
PQsendQuery
and
PQgetResult
is
retrieving large query results a row at a time. This is discussed in Section 31.5.
By itself, calling
PQgetResult
will still cause the client to block until the server completes the next
SQL command. This can be avoided by proper use of two more functions:
PQconsumeInput
If input is available from the server, consume it.
int PQconsumeInput(PGconn
*
conn);
PQconsumeInput
normallyreturns 1 indicating“no error”, butreturns 0 if there was somekind
of trouble (in which case
PQerrorMessage
can be consulted). Note that the result does not say
whether any input data was actually collected. After calling
PQconsumeInput
,the application
can check
PQisBusy
and/or
PQnotifies
to see if their state has changed.
687
Documents you may be interested
Documents you may be interested
