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: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
