From: Kemet
Date: May 5 2011 8:33pm
Subject: SSQLS with BINARY column is sometimes not escaped
List-Archive: http://lists.mysql.com/plusplus/9340
Message-Id: <2A2330FED8EB4F57A7A525B2309A0AF0@PCWillemQ>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit

I have a weird behaviour with the escaping of a BINARY column.

I'm using an SSQLS object representing the table rows.
When I first populate the items (coming from a TCP stream converted to a 
vector<unsigned char>), it apparently gets escaped when the "insert" query 
is built, and also when I use the object to build a custom query.
But when I afterwards read the data from the table into another SSQLS 
object, and then use this object to make the same query, the quote 
manipulator only quotes that field without escaping it (resulting in a bad 
query when for example the single quote character is part of the data).
I presume this is not intented behaviour. Or am I doing something wrong ?

Here is part of the code:

1. Table definition:
CREATE TABLE ChainBlocks
( ID   INTEGER UNSIGNED NOT NULL, hash  BINARY(32), prevhash BINARY(32), 
height  INTEGER, status  INTEGER, PRIMARY KEY (ID) );

2. Corresponding SSQLS object:
sql_create_5(ChainBlocks, 1, 5, mysqlpp::sql_int_unsigned, ID, 
mysqlpp::sql_blob, hash, mysqlpp::sql_blob, prevhash, mysqlpp::sql_int, 
height, mysqlpp::sql_int, status )

Note: first tried with mysqlpp::long_varbinary and also with other types of 
blob, all resulting in the same behaviour

3. Receiving the data from TCP stream:

 ChainBlocks newBlock(0);     // create new block to add, we don't know ID 
yet
 newBlock.hash.assign((char *)curBlockHash.begin(), 32);        // 
curBlockHash = calculated 256 bit hash of the block = array of 32 bytes
 newBlock.prevhash.assign((char *)&msgIn.vPayload[4], 32);  // 
msgIn.vPayload = vector<unsigned char> containing again a sequence of 32 
bytes
 newBlock.height=-2;   // height not yet known

 // --> see if prevBlockHash exists in existing blocks in ChainBlocks
 int iPrevHeight = mydb.GetBlockHeightFromHash(newBlock.prevhash);   <--  
this call works as intended: newBlock.prevhash gets quoted and escaped (see 
below for details)

4. Calling this same GetBlockHeightFromHash function using results from a 
query:

 vector<ChainBlocks> vChain;            // vector to store rows of select 
query
 int iChain, iHeight;
 mysqlpp::Query q = mySQL_con.query();
 do {
  q << "SELECT * FROM ChainBlocks WHERE status<1"; // get all blocks with 
unknown height
  q.storein(vChain);
  for (iChain=0; iChain!=vChain.size(); iChain++) { // loop through all 
blocks with unknown height
   iHeight = GetBlockHeightFromHash(vChain[iChain].prevhash);  // see if we 
have previous hash, and if so, what height is has
...}
while ...;

This call to GetBlockHeightFromHash with vChain[iChain].prevhash works 
incorrectly: it only gets quoted, NO ESCAPING is done :(
Note: prevhash itself contains the correct data (so it was stored and 
retrieved correctly).

5. GetBlockHeightFromHash function:

int dbBTC::GetBlockHeightFromHash(mysqlpp::sql_blob &fromHash)
{
 int i,j;
 mysqlpp::Query q = mySQL_con.query("SELECT ID, height FROM ChainBlocks 
WHERE hash=");
 q << mysqlpp::quote << fromHash;
 std::string s = q.str();        // only for debugging purposes
 mysqlpp::StoreQueryResult qres = q.store();
...
}

This function should get the height corresponding to the hash of the block 
with a simple SELECT query.

The only difference I can see is that in the first case, the ChainBlocks 
prevhash gets filled through an "assign(char *)", and in the second case it 
gets filled through the "storein" method.
But that should not change the escaping behaviour...