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

    Join Date
    Nov 2012
    Posts
    15
    Rep Power
    0

    How to handle slashes in mysql


    I have an mysql insert problem that I need some help with. I was trying to insert a record that contained a string that was a file path. I made a mistake when I typed the value into a textbox: "\test_email_archive/xxxx.com/box/inbox/2012-09/"

    As you can see I had the slash backwards. When the insert was done the record in mysql was valued:
    est_email_archive/emarotta.com/box/inbox/2012-09/, with the "\" and "t" truncated. I assume this occurred because the slash was handled as an escape character.

    I tried using addslashes and stripslashes, but I can't seem to get the syntax correct. The question I have is how to handle a situation where a user enters string into a text box with a "\" as part of the string so the value gets correctly entered into mysql (as entered)?
    The query I have as it stands is:
    $query = 'Insert into Email_StaffPath (StaffID, StaffPath) values(' . $StaffID . ',"' . $StaffPath . '")';
    I also tried it reversing the single and double quotes.
    $query = "Insert into Email_StaffPath (StaffID, StaffPath) values(" . $StaffID . ",'" . $StaffPath . "')";

    Thanks,
    Tom
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    182
    Rep Power
    83
    \t was actually taken a tab character, not just truncated.

    Have a look at the contents of the field with
    Code:
    select field_name, hex(field_name) from my_table;
    This should show you the invisible tab character lurking there.

    To make sure any slashes are correctly handled, you should pass any string through mysql_real_escape_string()

    Code:
    $query = "Insert into Email_StaffPath (StaffID, StaffPath) values(" . $StaffID . ",'" . mysql_real_escape_string($StaffPath) . "')";
    This will fix any slashes, and stray apostrophes as well.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    in addition to what BarryG already said:

    this little slash problem points to massive security holes in your query code. When certain characters break the query, there's obviously something wrong. What if people actively use this to mess with your database (aka SQL injection)?

    The problem is that you don't separate the actual query from the values you want to pass. It's all just one big string. So if users input a "wrong" value, it could easily interfer with the SQL (which is what just happened). To solve this, use prepared statements, which are available through the MySQLi extension or the PDO interface.

    If you're still using the old "mysql_" functions, throw them away. They are long obsolete and will die out sooner or later. If you cannot possibly do that, because you've already written 100,000 lines of code, well, then use mysql_real_escape_string() as suggested by BarryG. But this is really just a workaround for old code. In any other case, use PDO or MySQLi.

    Comments on this post

    • Northie agrees : PDO Rocks
  6. #4
  7. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    There's a link in my sig for migrating from mysql_* functions to PDO, it's worth a look

    Comments on this post

    • Jacques1 agrees : Nice link. :-)
    • aeternus agrees : cheers for sharing
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]

IMN logo majestic logo threadwatch logo seochat tools logo