#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    \

    as in

    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!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10

    Well


    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
  6. #4
  7. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    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.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    I don't know ruby but "sanitize" looks nice.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    41
    Rep Power
    10
    Oh but I need the HTML - trust me
    regular expressions are your friend
  14. #8
  15. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    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.
  16. #9
  17. No Profile Picture
    Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    57
    Rep Power
    0
    Hi,

    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 ...
  18. #10
  19. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    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.
  20. #11
  21. kill 9, $$;
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2001
    Location
    Shanghai, An tSín
    Posts
    6,898
    Rep Power
    3887
    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.

IMN logo majestic logo threadwatch logo seochat tools logo