MySQL ODBC Connector - mysql_real_query.... probably asked a lot

Author

mysql_real_query.... probably asked a lot

Eric Smith

2006-02-28, 8:28 pm

--Apple-Mail-2--591219584
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

OK, I'll bet you guys get a lot of this one, but I can't seem to find
the answer in the archives.

I have binary data that I want to store in a longblob. This is just
byte data.... null bytes are possible. So, I use mysql_real_query.
How do I format the char* query string?

Here's the way my format looks:
sprintf(queryString,
"update images set imageData=%p where imageID='%s
'",imageData,[imageID cString]);

and then I do the query:
result = mysql_real_query(the
Connection,queryStri
ng,strlen(theDBData)

+nBytes);

where strlen(theDBData)+nB
ytes gives the total byte count for
queryString. Well, I get an error message saying that I have an
error in my syntax. How do I format this properly?

Thanks,
Eric
--Apple-Mail-2--591219584--

Ludwig Pummer

2006-02-28, 8:28 pm

Eric Smith wrote:
> OK, I'll bet you guys get a lot of this one, but I can't seem to find
> the answer in the archives.
>
> I have binary data that I want to store in a longblob. This is just
> byte data.... null bytes are possible. So, I use mysql_real_query. How
> do I format the char* query string?
>
> Here's the way my format looks:
> sprintf(queryString,
"update images set imageData=%p where
> imageID='%s'",imageData,[imageID cString]);
>
> and then I do the query:
> result =
> mysql_real_query(the
Connection,queryStri
ng,strlen(theDBData)
+nBytes);
>
> where strlen(theDBData)+nB
ytes gives the total byte count for
> queryString. Well, I get an error message saying that I have an error
> in my syntax. How do I format this properly?
>
> Thanks,
> Eric

The documentation for mysql_real_query does explain that it can handle
null bytes, but what if your binary data contains single-quote? You get
a syntax error.

I see two options for you here:
1) use mysql_real_escape_st
ring() on the binary data before you build it
into the final query string
2) use the prepared statement API

If you go with option 1, you'll need to allocate another buffer twice
the size of imageData to hold the escaped version.

If you go with option 2, you can use the imageData buffer directly, but
you'll have to use the prepared statement functions instead of
mysql_real_query(). I've never used prepared statements in the C API so
I'm just going on what the documentation says.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links