October 16th, 2012, 10:04 AM
Inserting non letter characters into a varchar field
Is there a built in mysql function (or maybe field type that I'm missing) that will allow me to insert the names of people with non letter characters in their names (like a , or a ' or a .).
The names are not being entered manually but part of an automated ruby script that parses HTML data.
Any pointing in the right direction would be greatly appreciated
October 16th, 2012, 10:35 AM
INSERT INTO strings (string) VALUES ('O\'Hara');
Although I suspect that there's some other part of this problem that you've not told us!
October 16th, 2012, 10:38 AM
I told you it was being entered automatically, so I was looking for a built in mysql function to use the slashes. i can't enter the slashes automatically (that I know of yet). It's extracted automatically from another source, I can't edit it before hand and the inserst are automatic
October 16th, 2012, 11:35 AM
how are they being entered automatically, through a script? If your script was written in php you'd merely use mysql_real_escape_string and have no problem doing that.
other languages should also have ways of escaping characters. You would clean up the input on your front end.
October 16th, 2012, 11:40 AM
It's written in ruby (for a variety of reasons) and so far I haven't found a ruby gem or built in function for inserting the \ if needed. I probably could use a regex substitution if i was better at regex but I don't know how to use a replace on multiple characters to create a backslash and KEEP the character
October 16th, 2012, 02:18 PM
I don't know ruby but "sanitize" looks nice.
October 16th, 2012, 02:55 PM
Oh but I need the HTML - trust me
regular expressions are your friend
October 16th, 2012, 02:56 PM
i don't know Ruby either, but would be surprised if there wasn't some method to escape data. Without it you would open your database up to injection attacks.
October 16th, 2012, 09:35 PM
Of yourse you can escape strings in Ruby (with a method fittingly named "escape_string"). But as I already tried to explain in his other thread, you cannot "escape" dots or commas.
If he actually can't insert dots and commas in the database, there's definitely a bigger problem that no regex or "magical" escaping method will fix.
But unless we have the full code and concrete information about the database, I don't see we can do anything but speculate. Especially since this all is rather unclear and strange ...
October 16th, 2012, 10:31 PM
There shouldn't be a problem inserting . or , into a varchar or text field as long as the entire insert is enclosed by " and ". Single quotes aren't a problem either from what I've just tested. I'm using mysql 5.5.20 on my laptop and inserted . AND ' AND - AND , without having to escape any.
October 17th, 2012, 08:57 AM
I don't know Ruby either, but generally you should be using placeholders and bind parameters for something like this. That way, your database driver takes care of the appropriate escaping for you, depending on which database you're using.
Some examples and links here that might be useful.