It takes 11 lines of code to do this query in PostgreSQL with binary parameters, such as non-escaped strings or binary data. Here, I pass a parameter as a string, so have to escape it to prevent SQL injection attacks.

char *outTemp[3];
int outLengths[3];
int formats[3];
formats[0]=PQEXECPARAM_FORMAT_TEXT;
formats[1]=PQEXECPARAM_FORMAT_BINARY; // Always happens to be binary
formats[2]=PQEXECPARAM_FORMAT_BINARY; // Always happens to be binary
sprintf(query, “INSERT INTO FileVersionHistory(applicationID, filename, createFile, changeSetID, userName) VALUES (%i, $1::text, FALSE, %i, ’%s’);”, applicationID, changeSetId, GetEscapedString(userName).C_String());
outTemp[0]=deletedFiles.fileList[fileListIndex].filename;
outLengths[0]=(int)strlen(deletedFiles.fileList[fileListIndex].filename);
formats[0]=PQEXECPARAM_FORMAT_TEXT;
result = PQexecParams(pgConn, query, 1, 0, outTemp, outLengths, formats, PQEXECPARAM_FORMAT_BINARY);

With my new function, ExecVaridic, it’s one line of code.

result = ExecVaridic(“INSERT INTO FileVersionHistory(applicationID, filename, createFile, changeSetID, userName) VALUES (%i, %s, FALSE, %i, %s);”, applicationID, deletedFiles.fileList[fileListIndex].filename, changeSetId, userName.C_String());

Why has no one thought of this before?

I support binary data too.

result = ExecVaridic(“INSERT INTO fileVersionHistory(binaryData) VALUES (%c)”, binaryData, binaryDataLength);

%c is my own extension, to mean binary data followed by length.

No string escaping necessary.

Here’s the code:
AutopatcherPostgreRepository.cpp

See PostgreSQLInterface::ExecVaridic

Average Rating: 4.8 out of 5 based on 171 user reviews.

2 Responses to “”

  1. Oliver Smith says:

    Isn’t %c the “single character” mask?

    I haven’t used PG in a long time, but normally you “prepare” the query; the standard interface is usually something like this:

    db = databaseConnection() ;
    sth = prepareStatement(“INSERT INTO table (xval, yval, strval, dataval) VALUES (?, ?, ?, ?)”) ;
    db->execute(sth, arg1, arg2, arg3, arg4) ;

    It’s always preferable to try and avoid passing data to the SQL parser even if for no reason other than it’s more efficient to pass it a placeholder/token instead of your data in terms of time it takes to process.

  2. Oliver Smith says:

    e.g. from the Perl DBI man page:

    Placeholders and Bind Values

    Some drivers support placeholders and bind values. Placeholders, also
    called parameter markers, are used to indicate values in a database
    statement that will be supplied later, before the prepared statement is
    executed. For example, an application might use the following to
    insert a row of data into the SALES table:

    INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)

    or the following, to select the description for a product:

    SELECT description FROM products WHERE product_code = ?

    The “?” characters are the placeholders. The association of actual
    values with placeholders is known as binding, and the values are
    referred to as bind values. Note that the “?” is not enclosed in
    quotation marks, even when the placeholder represents a string.

Leave a Reply