SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Evil Genius MilchstrabeStern's Avatar
    Join Date
    Nov 2003
    Location
    Arizona
    Posts
    1,131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Case Sensitivity in DB Queries

    Hey guys,

    This is relating to the CMS "Joomla!"

    I have been using Joomla for a while to allow me more time for programming tools, and less dealing with site management. However, I want to find a way to removed the case sensitivity from the login process. I'm not sure how to go about this assuming I can find the code that checks the login.

    Any ideas?

    -Bryan
    ]

  2. #2
    SitePoint Addict Kokos's Avatar
    Join Date
    Nov 2005
    Location
    The Netherlands
    Posts
    205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Bryan,
    an easy solution is making it so that all the new registered members' passwords and usernames go through a strtoupper(making it uppercase) or strtolower(making it lowercase) function before they go to the database. And you do the same when somebody logs in.

    Taking over the web one pixel at a time.
    Currently working @ CodeCreators

  3. #3
    Evil Genius MilchstrabeStern's Avatar
    Join Date
    Nov 2003
    Location
    Arizona
    Posts
    1,131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So basically:

    When a user logs in, or creates a login, the string is converted to lowercase? This way, all the users and passwords in the database are already lowercase.

    I'll need to create a simple script that converts all existing users to lower case, but I don't know what to do with their passwords since they are encrypted. Any ideas?

    -Bryan
    ]

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    My suggestion would be to leave the data as it is, and continue storing data case-sensetive. This gives you more flexibility for future changes, or using the same login/passwords with other systems.

    You can make the login process case-insensetive by changing the input and stored data to lowercase at comparison only, without altering what's stored.

    I'd only do this for the username. Strong passwords are a good thing, if people want mixed cases in their passwords that should be encouraged not discouraged. You can't convert them to lowercase anyway if you used md5 encryption (very common) as it's a one-way encryption.

  5. #5
    Evil Genius MilchstrabeStern's Avatar
    Join Date
    Nov 2003
    Location
    Arizona
    Posts
    1,131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can make the login process case-insensetive by changing the input and stored data to lowercase at comparison only, without altering what's stored.
    Maybe, except, how would you do such a comparison. Since the queries are usually something like

    PHP Code:

    $query 
    "SELECT * FROM $table WHERE $id = $value"
    How would you get a comparison to work? Pull all the users first, put them in an array as lower case, then query the array?

    Confused...

    -Bryan
    ]

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $query "SELECT * FROM users WHERE LOWER(username) = '" strtolower($_POST['username']) . "' AND password = '" md5($_POST['password']) . "'";

    // 

  7. #7
    Evil Genius MilchstrabeStern's Avatar
    Join Date
    Nov 2003
    Location
    Arizona
    Posts
    1,131
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent Dan!

    Wasn't aware of the LOWER() option. Does it just compare a lower case value from the database to the lower case string created by strtolower? Is there an UPPER() function then?

    Thanks!

    -Bryan
    ]

  8. #8
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both mySQL and PHP have upper and lower functions, though called different things. You could also do:

    PHP Code:
     $query "SELECT * FROM users WHERE LOWER(username) = LOWER( '" $_POST['username'] . "' ) AND password = '" md5($_POST['password']) . "'"

    // 

  9. #9
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For a full list of what mySQL can do with Strings, see:
    http://dev.mysql.com/doc/refman/5.0/...functions.html

    There are lots of things mySQL can do that people don't know about. I ended up using the elt function to for a custom query to from a table that stored both personal and work information. This query would pull the work information if it was there, and if it wasn't it'd pull the home information.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •