List:General Discussion« Previous MessageNext Message »
From:Fred van Engen Date:December 27 2003 12:00pm
Subject:Re: How to use API to write blobs
View as plain text  
On Fri, Dec 26, 2003 at 09:35:42PM -0500, Angus March wrote:
> > Angus,
> >
> > On Fri, Dec 26, 2003 at 08:26:38PM -0500, Angus March wrote:
> > > I'm trying to use the API to write to a MEDIUMBLOB, but I'm getting a
> very
> > > vague error: something about a problem "near '' on line 1". I'm
> forumating
> > > the query string w/
> > >
> > > sprintf(query, "INSERT INTO support_files
> (session_id,file_type,file_body)
> > > VALUES (%ld,%ld,", sessionID,fileType);
> > >
> > > Then w/calls to things like memcpy, and unformatted reads from a stream,
> I
> > > append the blob to the end of that, and finalize it w/a ')'. I'm very
> >
> > Well, your blob data may contain a NUL character, which will end your
> > query string. It may contain quotes, a comma, ')' and other nasty stuff.
> > You can't expect the MySQL parser to understand when these characters
> > are part of your blob data and when they are meant to end your query or
> > separate your query parameters. It is all just one single (long) query
> > string that the parser needs to work with.
> >
> > So you will need to escape at least the following in your blob data:
> >
> > NUL because it is a C string terminator
> > '   because it would terminate your blob 'string'
> >
> [snip]
> 
> Are you sure this is how it works? If you need to escape stuff, you aren't
> dealing with binary data, you are dealing with a character string. The first

Correct. Queries are character strings, so you'll need to encode your
binary data as such.

> part starts off as a character string (with the "INSERT" and everything) but
> the manual explicitly calls blob data in a query "binary". Furthermore, it
> acknowledges the presence of string-unfriendly characters; specifically, the
> '\0'. Check out:
>
> http://www.mysql.com/documentation/mysql/bychapter/manual_Clients.html#mysql_real_query
> The quote I'm thinking of is:
>  You must use mysql_real_query() rather than mysql_query() for queries that
> contain binary data, because binary data may contain the `\0' character
> 

That's a confusing statement IMHO. It may not be necessary to escape the
NUL character, but you still need to create a valid query. There is no
way to do that other than putting your binary data into a quoted string
in your query. Again, how would the MySQL parser see where your binary
data ends? How would the parser distinguish two binary values from one
binary value? Binary is a property of the column. It has nothing to do
with queries per se.

"Values in CHAR and VARCHAR columns are sorted and compared in case-
insensitive fashion, unless the BINARY attribute was specified when the
table was created. The BINARY attribute means that column values are
sorted and compared in case-sensitive fashion according to the ASCII
order of the machine where the MySQL server is running. BINARY doesn't
affect how the column is stored or retrieved."
(http://www.mysql.com/documentation/mysql/bychapter/index.html#CHAR)

You might want to look at the example at this URL:

http://www.mysql.com/documentation/mysql/bychapter/index.html#mysql_real_escape_string


Regards,

Fred.

-- 
Fred van Engen                              XB Networks B.V.
email: fred.van.engen@stripped                Televisieweg 2
tel: +31 36 5462400                         1322 AC  Almere
fax: +31 36 5462424                         The Netherlands
Thread
How to use API to write blobsAngus March27 Dec
  • Re: How to use API to write blobsFred van Engen27 Dec
  • Re: How to use API to write blobsAngus March27 Dec
    • Re: How to use API to write blobsFred van Engen27 Dec
  • Re: How to use API to write blobsAngus March27 Dec
    • Re: How to use API to write blobsFred van Engen27 Dec
  • Re: How to use API to write blobsAngus March27 Dec
  • Re: How to use API to write blobsAndy Bakun27 Dec