PHP GET request using varchar datatype as input

Question

Sorry if the wording in the title is not correct (new to PHP). I'm trying to return results from a mysql db using below php scripts.

php script

<?php

require "conn.php";

$adopt_id = $_GET["adopt_id"];

  $query = "
select *
from temp_table
where adopt_id = $adopt_id
";

....
?>

Now if I run the above in my browser as url below, it returns as expected http://localhost/searchfeed.php?adopt_id=1

Dump of above query:

select *
from temp_table
where adopt_id = 1

Same php script but filtering on a diff field which is of varchar data type.

php script

    <?php

    require "conn.php";

    $GENDER = $_GET["gender"];

      $query = "
    select *
    from temp_table
    where gender = $GENDER
    ";

    ....
    ?>

Now if I run the above in my browser as url below, it returns null because its not getting any results = http://localhost/searchfeed.php?gender=M

I dumped the above query to a log file, seems like it doesn't do anything with the $GENDER. This is what the query looks like

select *
from temp_table
where gender =

You'd need quotes: `select * from temp_table where gender = '$GENDER'` — brombeer, Feb 05 '18 at 12:10
You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of injecting user inputs directly into your queries. Specially since you're not escaping the user inputs at all! — M. Eriksson, Feb 05 '18 at 12:12

score 1 · Answer 1 · answered Feb 05 '18 at 12:13

1

This is because you need to put non-numeric values within single quotes.

select *
from temp_table
where gender = '$GENDER'

Please also have a look at Prevent SQL Injections

answered Feb 05 '18 at 12:13

Scriptman

424
2
13

score 0 · Answer 2 · answered Feb 05 '18 at 12:33

The correct should be this way.

<?php

require "conn.php";

$GENDER = $_GET["gender"];

  $query = "
select *
from temp_table
where gender = '".$GENDER."'";

....
?>

This way our query will work even ig the $GENDER value is a string. In the previous answeres the mysql will return an error.

Maki Vlach · Accepted Answer · 2018-02-05T14:42:10.900

-1

I don't see any problem with your PHP code in your second statement, but as suggested by others, your MySQL query consists of incorrect statement.

Please, update your MySQL statement to look either like:

select *
from temp_table
where gender = '$GENDER'

or

select *
from temp_table
where gender like '$GENDER'

Also consider using any MySQL library, as such usually consists of various security patches (as noted in the previous answer - the SQL injections)

or at least use mysqli_real_escape_string() function

Edit: Problem solved most probably by incorrect variable name spelling. GET variables are all stored in $_GET array.

your value of gender can be accessed by this statement: $gender = $_GET['gender'];

note: for others - be careful to spell variable like $_GET not $GET

edited Feb 05 '18 at 14:42

answered Feb 05 '18 at 12:20

Maki Vlach

177
3
11

I already tried $GENDER in single quotes earlier and doesn't work. If I put it in single quotes and check the sql dump it just shows where gender = '' . w.r.t sql injection - In my other php scripts I use prepared statements and mysqli_real_escape_string(), the script I shared here was just for testing purposes. – Dean Lambrechts Feb 05 '18 at 12:36
Dean Lambrechts If you put `echo $_GET['gender'];`, what your output is? – Maki Vlach Feb 05 '18 at 12:45
it echo's M which is what I'm passing. Actually echo'ing Mnull, but that's because its not getting any data. – Dean Lambrechts Feb 05 '18 at 12:51
What if you try: `"select * ...... WHERE gender like " . $GENDER`; (string appending - don't forget space after `like`) – Maki Vlach Feb 05 '18 at 12:55
This is what the output of the query then looks like "WHERE gender like " Still nothing after the like. Its almost like its not resolving the M, because why would the echo $GET['gender'] work but not when I try to add it to my query. Odd... – Dean Lambrechts Feb 05 '18 at 13:07
It's really weird. What if you rename your variable? Like `$sex = $_GET['sex'];`? It's unlikely, but it might be conflicting somewhere. – Maki Vlach Feb 05 '18 at 13:10
Nope still nothing. I even went the route of creating a new script and rather using the mysqli_prepare and mysqli_bind , but if I check the log on mysql db for incoming queries, there where is still just ....where gender = '' – Dean Lambrechts Feb 05 '18 at 13:28
Could you please provide your output from: `echo var_dump($_GET);`? – Maki Vlach Feb 05 '18 at 14:00
array(1) { ["a"]=> string(1) "M" } null (I changed the var name to a from your earlier suggestion) – Dean Lambrechts Feb 05 '18 at 14:08
Well, that is weird. I see that you requested this url: `http://localhost/searchfeed.php?a=m`. Also from I can see is that your URL variable is successfully passed into your `$_GET` variable, which is very good. That variable should be now accessible from following declaration: `$a = $_GET['a'];` After that, please call `echo $a;` to see if your parameter has been passed successfully. – Maki Vlach Feb 05 '18 at 14:16
$a = $GET['a']; echo $a; null – Dean Lambrechts Feb 05 '18 at 14:29
Be careful with the underscore. The variable name is `$_GET['a']` not `$GET['a']` – Maki Vlach Feb 05 '18 at 14:31
aaah well spotted. Its now working in my query. Not sure how it happend now because it was originally $_GET even in my above examples. Think too much was going on now. Thanks so much for all your efforts, I really appreciate it. – Dean Lambrechts Feb 05 '18 at 14:34
No problem, mate. I'll update my original post with the solution. Could mark it as solved then, please? :) – Maki Vlach Feb 05 '18 at 14:36

PHP GET request using varchar datatype as input

3 Answers3