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

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189

    Case sensitive tables and fields


    A client has a PHP application that was written to work on a Windows XAMPP mySQL. I am trying to port this over to Linux, however the original author seems to have made no effort to stick to a case standard.

    Is it possible to set mySQL, maybe via table or database specific settings, to ignore case on a Linux server?
    Thomas Tremain
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Originally Posted by Jacques1
    I've seen that, but it appears to affect mySQL server wide, not just for a single database, and if I have read correctly, may require renaming all tables, and fields to their lowercase equivalent.
    Thomas Tremain
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Also, if this is a global setting, it won't work on a shared server.
    Thomas Tremain
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    I'm not aware of any other way.

    Maybe the easiest solution would be to actually fix the problem in the source code (unless we're talking about millions of lines of code).
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Originally Posted by Jacques1
    I'm not aware of any other way.

    Maybe the easiest solution would be to actually fix the problem in the source code (unless we're talking about millions of lines of code).
    Not millions of lines, but enough to warrant looking for a quicker method....
    Thomas Tremain
  12. #7
  13. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Originally Posted by ttremain
    Not millions of lines, but enough to warrant looking for a quicker method....
    Run it on Windows as the not-so-picky original author.

    Unfortunately the only solutions as I can see are the ones suggested in this thread.

    But then again:
    1. Loading the source code in some VCS
    2. and performing something like perl -pni.bak -e "s/YourTableName/YourTableNameWithCorrectCase/gi" OnEverySourceCodeFileInLoop.php
    # The i in the /gi at the end says to match non-case-sensitive to get all permutations of the table name
    # Possibly adding a space in front and in the back of the strings in the substitution in case the table name also clashes with variable names in the code
    3. Check the diff to verify that only correct strings are substituted and test run.

    Since fields are always case insensitive you only need to do this on database names and table names so there shouldn't be that many to replace.
    Last edited by sr; August 14th, 2013 at 09:19 AM.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo