mysql和php突然区分大小写？

发布于 2024-10-13 02:22:07 字数 318 浏览 3 评论 0原文

基本上，我改变了我的 php 登录代码中的一些内容，现在当我登录时，MySQL 要求大小写匹配？！

    $match = "select * from users where username = '".$username."' and password = '".$password."';";

    echo $match;

如果我的用户名的 CaSe 与数据库不匹配，则它不会返回任何行。

如果用户注册为 ADMIN，那么我希望他们能够以“admin”或“AdMiN”等身份登录。有什么想法吗？

非常感谢:)

原文

Basically, I've changed something in my php login code and now when I log in, the MySQL requires that the case matches?!

    $match = "select * from users where username = '".$username."' and password = '".$password."';";

    echo $match;

If my username's CaSe doesn't match the database then it doesn't return any rows.

If a user registers as ADMIN then I want them to be able to log in as "admin" or "AdMiN" etc. any ideas?

Many thanks :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

栀子花开つ 2024-10-20 02:22:07

我猜该列的排序规则已更改。请参阅有关字符串比较中区分大小写的 MySQL 页面了解更多。为了使自己免受此类问题的影响，

$match = "select * from users where UPPER(username) = '".strtoupper($username)."' and password = '".$password."';";

通常的技巧是：不过，正如其他人所观察到的，了解排序规则在数据库中的工作原理并正确使用它是最好的解决方案。正确设置数据库排序规则并依赖它应该允许数据库使用用户名列上的任何索引，而不是扫描表。

并且还要确保您避免 SQL 注入攻击 - 如果您当前的代码中会发生什么用户输入包含单引号的用户名？

您可能还需要考虑允许使用哪些字符作为用户名，以及您使用的字符集 - “René”是有效的用户名吗？它会成功地从前端通过数据库并返回而不会受到字符集问题的影响吗？ Joel Spolsky 的关于 Unicode 的优秀文章在某些时候可能值得一读。

The collation for that column has changed, I guess. See this MySQL page about case sensitivity in string comparison to learn more. To insulate yourself from problems like that, something like:

$match = "select * from users where UPPER(username) = '".strtoupper($username)."' and password = '".$password."';";

is the usual trick. As others have observed, though, understanding how collation works in the database and using it appropriately is the best solution. Getting the database collation right and relying on it should allow the database to use any index on the username column, rather than scanning the table.

And also make sure that you're avoiding SQL injection attacks -- what happens in your current code if a user enters a username containing a single quote?

You may also want to consider what characters you'll allow as usernames, and what character set you're using -- is "René" a valid username? Will it successfully pass from your front-end, through the database and back out unmolested by character set problems? Joel Spolsky's excellent article on Unicode is probably worth a read at some point.

回复收藏 0 原文

~没有更多了~