Simple MySql query is not working in php

Question

So i was trying to execute this simple query:

SELECT * 
FROM users 
WHERE Username = 'xyz' 
AND OS = 'Windows 10/Server 2016' 
AND HWID = 'FFrWkNSa1l3TURjek1FWXdNUT09' 
AND MACAddress = '33D255CCFDAD'

This is what i tried alongside many others:

$q1 = "SELECT * FROM users WHERE Username = '".urldecode($_GET['userName'])."' AND OS = '".urldecode($_GET['OS'])."' AND HWID = '".$HWID."' AND MACAddress = '".$mac."'";
$systemMatches = $mysqli->query($q1);
 if($systemMatches->num_rows == 1)
{
 echo"Valid System";
}else{
 echo"Invalid System";
}

ALL Variables are beeing grabbed from the url-parameter and 100% CORRECT (I have triple checked.)

Maybe something is wrong with the variable OS...? This is the raw version of OS: Windows%2010/Server%202016.

As you can see, it includes 2x spaces. I handled the spaces by using urldecode(). I even checked via echo and it decoded the spaces perfectly: Windows 10/Server 2016. But still something not working, would appreciate any kind of help as i am pretty new to mysql, thanks!

PS: That query is working fine when executing in phpmyadmin with the same given variables.

UPDATED CODE - STILL PRINTING: INVALID SYSTEM :(

$userName = $mysqli->real_escape_string($_GET['userName']);
    $OS = $mysqli->real_escape_string($_GET['OS']);
    $HWID = $mysqli->real_escape_string($_GET['HWID']);
    $mac = $mysqli->real_escape_string($_GET['mac']);

    $q1 = "SELECT * FROM users WHERE Username = '".$userName."' AND OS = '".$OS."' AND HWID = '".$HWID."' AND MACAddress = '".$mac."'";
    $systemMatches = $mysqli->query($q1);
    if($userExists->num_rows == 1)
    {
     if($systemMatches->num_rows == 1)
    {
     echo"Valid System";
    }else{
     echo"Invalid System";
    }

You could start by adding error handling to the MySQL query, just in case there is a MySQL error. — Shadow, Oct 21 '16 at 15:03
Echo your query to make sure it's what you think it is, and run that in the console. Since you're using mysqli, prevent SQL injection by using prepared statements with bind_param. — aynber, Oct 21 '16 at 15:03
I even echoed my query, it's the exact same query as posted above. All parameters are correct yet i'm still receiving "invalid system" :( Thanks any more guesses? — Noob02017, Oct 21 '16 at 15:05
Consider updating your question with the error you are getting. — nyedidikeke, Oct 21 '16 at 15:07
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Oct 21 '16 at 15:12
You *are* getting an error. Have you looked at your error logs? With proper query prep you would not have to use `urldecode()` — Jay Blanchard, Oct 21 '16 at 15:14
@JayBlanchard Please take a look above at my Updated code. I'm getting not a single error even with enabled errorlogging. BTW: I'm using this piece of code local via Xampp. No need to worry about SQLInjectionAttacks yet. Thanks — Noob02017, Oct 21 '16 at 15:17
The problem is that you are looking for * from user where user = and it is likely there is more than one. You are evaluating for exactly one. Go into mysql and run the query. How many duplicates are you getting? In fact, add "host" into the select statement to see whether there are the same username at different hosts. — T Gray, Oct 21 '16 at 15:19
@TGray Thank you, that solved it. There is a duplicate, how did i forgett about that one lol. Thanks aggain - solved. — Noob02017, Oct 21 '16 at 15:21
I hate when people say *"I'm not that far along..."* or *"This site will not be public..."* or *"It's only for school, so security doesn't matter..."*. If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, *"I'll add security later..."* or *"Security isn't important now..."* or *"Ignore the security risk..."*. If you don't have time to do it right the first time, when will you find the time to add it later? — Jay Blanchard, Oct 21 '16 at 16:51

Mohd Abdul Mujib · Answer 1 · 2016-10-21T15:20:49.313

1

First of all you dont need to "urldecode" manually, if the value is coming in from $_GET then php will take care of all the decoding when you access the value using $_GET['xyz'].

Also its the escaping that may be creating the problem, so just use

...

$userName = $mysqli->real_escape_string($_GET['userName']);
$os = $mysqli->real_escape_string($_GET['OS']);

$q1 = " SELECT * FROM users WHERE Username = '". $userName ."' AND OS = '". $os ."' AND HWID = '".$HWID."' AND MACAddress = '".$mac."' 
       LIMIT 1 ";
...

edited Oct 21 '16 at 15:20

answered Oct 21 '16 at 15:04

Mohd Abdul Mujib

13,071
8
64
88

Thanks. php did not take care of decoding. The raw variable OS when echoing printed with 2x %20's. – Noob02017 Oct 21 '16 at 15:07
escape_string did not help either. Any more guesses? Thanks. – Noob02017 Oct 21 '16 at 15:07
1

nvm it actually did take care of decoding lol thanks but the query is still not working :( – Noob02017 Oct 21 '16 at 15:09
@Noob02017 it looks like you are unknowingly `urlencoding` twice on the frontend. Could you try removing the "urlencoding" function/method from the frontend and let the library do the work. – Mohd Abdul Mujib Oct 21 '16 at 15:09
My new code - still printing: INVALID SYSTEM $userName = $mysqli->real_escape_string($_GET['userName']); $OS = $mysqli->real_escape_string($_GET['OS']); $HWID = $mysqli->real_escape_string($_GET['HWID']); $mac = $mysqli->real_escape_string($_GET['mac']); $q1 = "SELECT * FROM users WHERE Username = '".$userName."' AND OS = '".$OS."' AND HWID = '".$HWID."' AND MACAddress = '".$mac."'"; $systemMatches = $mysqli->query($q1); if($userExists->num_rows == 1) { if($systemMatches->num_rows == 1) { echo"Valid System"; }else{ echo"Invalid System"; } – Noob02017 Oct 21 '16 at 15:13
@Noob02017 where is this variable `$userExists` coming from? – Mohd Abdul Mujib Oct 21 '16 at 15:18
@Noob02017 as @T gray has commented, its quiet possible that there are multiple users with same username so maybe try limiting the result. – Mohd Abdul Mujib Oct 21 '16 at 15:21
yea that solved it. $UserExists is part of the code too, lazy c&P. anyways thanks for your efforts too – Noob02017 Oct 21 '16 at 15:23

Simple MySql query is not working in php

1 Answers1