Page 1 of 1

Get case sensitive query

Posted: Tue Dec 13, 2011 10:40 am
by Amanda1998
Hi you all.. first the code

Code: Select all

function checkSecAnswer($userID,$answer)
{
global $mySQL;
	if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND `answ` = ? LIMIT 1"))
	{
		$answer = ($answer);
		$SQL->bind_param('is',$userID,$answer);
		$SQL->execute();
		$SQL->store_result();
		$numRows = $SQL->num_rows();
		$SQL->close();
		if ($numRows >= 1) { return true; }
	} else {
		return false;
	}
}
They have to answer the Sec question, witch is stored as "Lower and Upper Cases Sec Question", but if they type "myanswer" or "MYANSWER", they still get access anyway... how to fixed guys please..thanks ... cheers

Re: Get case sensitive query

Posted: Tue Dec 13, 2011 12:24 pm
by mikosiko

Re: Get case sensitive query

Posted: Tue Dec 13, 2011 1:39 pm
by Amanda1998
Thanks @mikosiko, still not luck at all...
if i do

Code: Select all

	if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND LOWER(`answ`) = ? LIMIT 1"))
	{
		$answer = strtolower($answer);
Tha do it, but students can type their Sec Question lowercase even on MySQL table have been stored like "MyAnswER" , witch is the case that I need.. any help?

Re: Get case sensitive query

Posted: Tue Dec 13, 2011 2:03 pm
by mikosiko
did you really read the link that I provide?... the last code that you posted doesn't have nothing related to that link

Re: Get case sensitive query

Posted: Tue Dec 13, 2011 2:18 pm
by Amanda1998
I did, but dont get it :(

Re: Get case sensitive query

Posted: Tue Dec 13, 2011 2:38 pm
by mikosiko
let see.... this paragraph doesn't ring a bell for you? (Disclaimer: I'm not trying to be rude or nothing similar... just trying to guide you in a different way)
The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:
so...
step 1: Find your character set and collation...
to find the collation:

Code: Select all

SELECT COLLATION(VERSION());
step 2: Force one of the operands to have a case sensitive or binary collation... p.e (assuming that your collation is utf8_general_ci)

Code: Select all

          if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND `answ` = BINARY ? LIMIT 1"))  or

         if ($SQL = $mySQL->prepare("SELECT `username` FROM `students` WHERE `id_user` = ? AND LOWER(`answ`) = ? COLLATION utf8_bin LIMIT 1"))  // assuming that your collation is utf8_general_ci

Re: Get case sensitive query

Posted: Tue Dec 20, 2011 9:33 am
by Amanda1998
(Disclaimer: I'm not trying to be rude or nothing similar... just trying to guide you in a different way)
:roll: @mikosiko ... You have been nice to me, don't worry... And yes that solve my issue.. thank you very much..
My issue has been solved ...