1

I have a table with this structure:

+----+-----------+
| id | user_pass |
+----+-----------+
| 1  |    01     |
| 2  |    10     |
+----+-----------+

And here is my query:

SELECT COUNT(*) FROM users WHERE user_pass = '$pass' limit 1

The problem:

$pass = '10';  // output: 1

$pass = '01';  // output: 0

while both values 10 and 01 are exist in the table, Why the output is different for each of them ?

Here is a fiddle.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
Shafizadeh
  • 9,960
  • 12
  • 52
  • 89
  • 2
    when you insert into the table 01 is cast to integer before writing to varchar.. put quotes around `'01'` and it works. [corrected fiddle](http://sqlfiddle.com/#!9/6e5fe/2) – amdixon Aug 16 '15 at 11:57
  • @amdixon yes, you are right, thanks – Shafizadeh Aug 16 '15 at 12:03
  • @rekire: It's unfortunate that the actual bug wasn't mentioned in the question at all. This question is, ultimately broken and unsalvageable. Still, as currently written, it's a MySQL question. In general, questions tagged [tag:php],[tag:mysql] should be extremely rare, and right now I don't see a reason for this to be one of them. – Lightness Races in Orbit Aug 16 '15 at 18:13

1 Answers1

3

Quotes are your friend. When you insert the rows you had no quotes this results that you input is interpreted as integer. So 01 gets 1 this explains why you get no results.

so your SQL should look like this:

INSERT INTO `users` VALUES (NULL, '01');
INSERT INTO `users` VALUES (NULL, '10');

Here as fiddle: http://sqlfiddle.com/#!9/6e5fe/1/0

The real issue (which can been found in the comments) is that PDO was used to insert the data into the database. The solution is to enfore to store the strings as string. For some reasons the values where automatically converted to integers, so the leading zero get lost.

The best way to enforce that the string will be a string is to cast it explicitly to string like this:

$password = (string)$_POST['password'];
kelunik
  • 6,750
  • 2
  • 41
  • 70
rekire
  • 47,260
  • 30
  • 167
  • 264
  • thats fine, was a simple enough question – amdixon Aug 16 '15 at 12:04
  • Thanks for answer, But I use PDO for inserting: `insert into table(password); execute(array($password));` So how should I use of quotes in my query ? – Shafizadeh Aug 16 '15 at 12:05
  • @Sajad how do you set the value to `$password`? I guess there is your bug. – rekire Aug 16 '15 at 12:08
  • like this: `$password = $_POST['password'];` – Shafizadeh Aug 16 '15 at 12:09
  • what did you do? combining the password with a empty string ? why really ? I think it is better: `$password = '. $_POST['password'].';`, of course I don't know it works or not ... – Shafizadeh Aug 16 '15 at 12:14
  • iIn your opinion is it better I use `''.` before all inputs ? `''.$name`, `''.$email` and ... ? – Shafizadeh Aug 16 '15 at 12:40
  • Regarding [this question](http://stackoverflow.com/q/28098/995926) `password = (string)$_POST['password'];` would be the first choice. – rekire Aug 16 '15 at 13:09
  • @rekire very good !! thanks. +1 voteup for this point `(string)`. And is it better to I store all data as string ? like *name*, *email*, *cellphone* and ... or not ? – Shafizadeh Aug 16 '15 at 16:12