|
KaptainKrayola
|
 |
« on: November 19, 2009, 03:30:07 PM » |
|
So, the Kaptain here has been running into issues with inserting big blocks of syndicated text into his DB where sometimes it will just fail with some kind of goofy error saying there is an issue with the syntax of my query (which there totally isn't cause my queries are teh win). So, I'm wondering what the best method(s) for escaping and making big blocks of text mySQL friendly for insertion into the table.
Discuss in 3, 2, 1...GO!
|
|
|
|
|
Logged
|
We can't stop here, this is bat country.  |
|
|
|
lamontagne
|
|
« Reply #1 on: November 19, 2009, 04:31:24 PM » |
|
Load data infile or properly escaped batch inserts. That being said I find it easier to store huge chunks of text in a file and store the path plus only what is needed in the db
|
|
|
|
|
Logged
|
"Long time no see. I only pray the caliber of your questions has improved." - Kevin Smith
|
|
|
|
perkiset
|
|
« Reply #2 on: November 19, 2009, 04:47:53 PM » |
|
Am I missing it here? $newbuff = mysql_escape_string($oldBuff); ...?
you've probably got some double or single quotes in it.
|
|
|
|
|
Logged
|
It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
|
|
|
|
nutballs
|
|
« Reply #3 on: November 19, 2009, 04:50:29 PM » |
|
I will again recommend my old standby.
base64 encode it
OR
hex encode it
Although there are function out there built into assorted languages that are supposedly able to escape any mess of shit you throw at it, I have found them all to be PHAIL. I gave up eons ago, though I keep trying them out.
hex or b64 are ZERO danger to your database, no matter what.
|
|
|
|
|
Logged
|
I could eat a bowl of Alphabet Soup and shit a better argument than that.
|
|
|
|
KaptainKrayola
|
|
« Reply #4 on: November 19, 2009, 07:07:56 PM » |
|
Am I missing it here? $newbuff = mysql_escape_string($oldBuff); ...?
you've probably got some double or single quotes in it.
been there, done that - still had issues. I had thought that was the way to go but still run into the odd insert that fails. Tried a variety of other things as well but still can't get it to 100% success rate so i thought i may be missing something. @nuts - clever idea with hex or base64 i may have to give that a whirl. |
|
|
|
|
Logged
|
We can't stop here, this is bat country. |
|
|
|
nutballs
|
|
« Reply #5 on: November 19, 2009, 08:47:38 PM » |
|
between smart slashes smart quotes smart smartyness and all that escape shit, and it being different on every damn system, Im surprised mysql ever works with text being inserted.
I think another option would be binary, but I never have bothered to try it.
|
|
|
|
|
Logged
|
I could eat a bowl of Alphabet Soup and shit a better argument than that.
|
|
|
|
oldenstylehats
|
|
« Reply #6 on: November 19, 2009, 08:59:57 PM » |
|
I will again recommend my old standby.
base64 encode it
OR
hex encode it
Although there are function out there built into assorted languages that are supposedly able to escape any mess of shit you throw at it, I have found them all to be PHAIL. I gave up eons ago, though I keep trying them out.
hex or b64 are ZERO danger to your database, no matter what.
Thank you. I'd never thought of that before. Great idea. |
|
|
|
|
Logged
|
No links in signatures please
|
|
|
|
nutballs
|
|
« Reply #7 on: November 19, 2009, 10:03:12 PM » |
|
Its a  for most folks when they hear it. the hard part is remembering to do it... lol I always forget. downside is searchability. You cant really do LIKE queries or FULLTEXT. But generally speaking, it would only be used for BIGASSTEXT or dirty text. Shorter text tends to have less probability of some magic quote combo that breaks the escape functions. |
|
|
|
|
Logged
|
I could eat a bowl of Alphabet Soup and shit a better argument than that.
|
|
|
|
perkiset
|
|
« Reply #8 on: November 19, 2009, 11:05:14 PM » |
|
It's for that very reason that, although I like the concept, I don't like it for pure text content.
Kaptain have you thought about doing a straight removal of all non-ascii chars, then dump backslashes, then escape it? That should just about do it. If that doesn't, PM me a SQL that fails. I think it's simpler than this.
|
|
|
|
|
Logged
|
It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
|
|
|
|
arms
|
|
« Reply #9 on: November 20, 2009, 01:34:53 PM » |
|
i know i have had headaches from character encoding problems when pulling shit from the web.
documents are not always using the character set they claim to be.
at least make sure the columns you are inserting into are utf and not latin.
|
|
|
|
|
Logged
|
|
|
|
|
KaptainKrayola
|
|
« Reply #10 on: November 20, 2009, 03:34:09 PM » |
|
thanks for the suggestions all - i'm going to hammer on it some and see if there is anything i have missed.  |
|
|
|
|
Logged
|
We can't stop here, this is bat country. |
|
|
|
herbacious
|
|
« Reply #11 on: December 16, 2009, 08:39:48 AM » |
|
prepared statements any use?
|
|
|
|
|
Logged
|
|
|
|
|
