Problem inserting serialized array

[learning_brain]

I can't get my head round this....

I have an array which I want to store to the DB (I won't even go into the reasons for this!) but am having problems.

The serialized array string contains double quotes, so I have to be careful.

$query_insert_summary = sprintf('INSERT INTO IMGCOMPS (DESCRIPTION, ANALYSIS) VALUES ("Test","%s")',$serialisedSummaryArray);
$result_insert_summary = mysql_query($query_insert_summary, $discountdomains) or die(mysql_error());

I have tried without the sprintf method as well.

I'm getting back an error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Highlights";i:0;s:9:"Lowlights";i:0;s:5:"Greys";d:49;s:5:"Flesh";d:7.00000000000' at line 1

I tried the following structure too...

$query_insert_summary = 'INSERT INTO IMGCOMPS (DESCRIPTION, ANALYSIS) VALUES ("Test",$serialisedSummaryArray)';

But I get back

Unknown column '$serialisedSummaryArray' in 'field list'

I can't wrap the serialized array in double or single quotes here without causing problems.

Any ideas?

 

[misson]

Use a PDO prepared query. Since values are sent out-of-band, metacharacters are a non-issue.

 

[learning_brain]

Hmm - good try but PDO is disabled with my host.

I might have to do implode/explode

 

[descalzo]

$query_insert_summary =
 "INSERT INTO IMGCOMPS (DESCRIPTION, ANALYSIS) VALUES ('Test','$serialisedSummaryArray')";

Does this give you an error?

 

[learning_brain]

OMG - Yes - I thought this was the first option I tried.... obviously not lol.

Thanks.

 

[lemon-tree]

Hmm - good try but PDO is disabled with my host.

I might have to do implode/explode

Use mysqli then, I believe that has support for prepared queries too. Frankly any host that disables PDO is more than likely just being too lazy to update their PHP. So basically, avoid using the old mysql driver like the plague, it is twice outdated and lacks a considerable amount of security you could get from using the newer techniques.
If you insist upon using the mysql driver, look at using mysql_real_escape_string on the input, this will escape all the quotes and other characters in the string.

Edit: yes, a quick check reveals mysqli does have support for preparing queries, so I strongly suggest you try and move your code over to using it.

 

[learning_brain]

Thanks for all the help.

I would have used individual fields, but I am writing an image analysis class and I wanted to provide an image "fingerprint" for each image type to compare with. Each fingerprint is kinda complex!

 

[essellar]

If anyone ever asks what NoSQL (schemaless) databases are good for, this is a perfect example. It's probably not an option (given that even PDO isn't supported), but Apache CouchDB is probably the optimal store for this kind of data.

(If you've never worked with a document store before, it takes some time to wrap your head around it. CouchDB is part of the standard Ubuntu distro if you want to play with it. If your app gets enough traction to require the kind of server space you can control, it will probably allow you to include features that are impractical using an RDBMS. CouchDB, and its spiritual parent, the Notes data store, will let you do marvelous things with arbitrary data, at the cost of some efficiency in dealing with regularly-structured data.)