MySql database query not working when I use _GET inside

Question

I am a php beginner.

I have the following script which works if I do not use _GET['version'] in the query, but works if I remove it. There is no error; I am not sure why it is not working.

<?php

    // Specify your table name
    $hostname = 'localhost';
    $dbname = 'stats';
    $table_name = 'st_stats';

    $username = 'test';
    $password = 'test';  

    try
    {

        $conn  = new PDO("mysql:host=$hostname;dbname=$dbname",$username,$password);
        //By default mode is silent and exception is not thrown. So I it to throw ex
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


       // If the query is like this no error is given but page shows up blank
       $stmt = $conn->query("SELECT * FROM $table_name where version = $_GET['version']", PDO::FETCH_ASSOC);
       // This works if uncomment below line instead and comment line above
       //$stmt = $conn->query("SELECT * FROM $table_name", PDO::FETCH_ASSOC);

        $count = $stmt->rowCount();
        echo("<h1>currently $count records</h1>");

    } 
    catch(PDOException $e) 
    {
        echo 'ERROR: ' . $e->getMessage();
    }    

?>

I want to access the page like this

 http://www.mydomain/records.php?version=1.2

Note that version column does exit in the table

`SELECT * FROM $table_name where verion = $_GET['version']` Did you mean `version` and not `verion` here? — jk., Apr 20 '14 at 21:24
`"SELECT * FROM $table_name where verion = '.$_GET['version'].'", PDO::FETCH_ASSOC`'may be correct im not sure try it — Dev Man, Apr 20 '14 at 21:26
You need to enclosed your variable in braces like this: `{$_GET['version']}` If you do you'll be wide open to SQL Injection attacks, so be very sure you've validated and sanitized your variable first. — , Apr 20 '14 at 21:27
@jk corrected that in the question, it is version but does not work — Ahmed, Apr 20 '14 at 21:29
don't do that! you are exposing you to the easiest SQL injection vulnerability exists! escape the parameter and validate its format before you inject it into the query (!!!!), or otherwise use prepared statements/stored procedures — Yaron U., Apr 20 '14 at 21:32

score 1 · Accepted Answer · answered Apr 20 '14 at 21:31

You could try to avoid a bit of sql injection here by preparing the statement properly:

$v_term = $_GET['version'];
$query = "SELECT * FROM $table_name where version = :term";
$result = $conn->prepare($query);
$result->bindValue(":term",$v_term);
$result->execute();

Also, run the statement straight from the db if you can to make sure you are getting records back. Other than that, there is no other way to debug this for you from what you given us.

Maggz · Answer 2 · 2014-04-20T21:35:01.133

0

This doesnt work because you're trying to access $_GET['version'] an array variable within a string here

"SELECT * FROM $table_name where version = $_GET['version']", PDO::FETCH_ASSOC

placing {} around the variable will fix this one issue $stmt = $conn->query("SELECT * FROM $table_name where verion = {$_GET['version']}", PDO::FETCH_ASSOC);

But you should also sanitize this value before you put it right int a sql statement

edited Apr 20 '14 at 21:35

answered Apr 20 '14 at 21:24

Maggz

175
9

A single line of code without any explanation is not an answer. – j08691 Apr 20 '14 at 21:26
1

Watch for SQL Injection. This is dangerous. – Apr 20 '14 at 21:29

score 0 · Answer 3 · answered Apr 20 '14 at 21:26

Maybe version is not an integer therefore need quotes ?

"SELECT * FROM $table_name where verion = '".$_GET['version']."'",

Anyway you are vulnerable to sql injection and also misusing PDO

You should at least bindParam/bindValue

Or use execute() and past the $_GET value

vvanasten · Answer 4 · 2014-04-20T21:36:05.703

You have verion rather than version in your query. You're also not passing the value of $_GET['version'], you're passing the string "$_GET['version']" right into the query. Update your query to this:

$stmt = $conn->query("SELECT * FROM $table_name where version = {$_GET['version']}", PDO::FETCH_ASSOC);

Wrapping a variable that's inside a double quoted string ("") in curly braces ({}) evaluates to the value of the variable.

If you do this you will be wide open to SQL injection attacks. Be sure to sanitize the variable before you run the query, or better yet consider prepared statements.

score 0 · Answer 5 · edited May 23 '17 at 12:28

As documented under Variable parsing:

There are two types of syntax: a simple one and a complex one.
[ deletia ]
Example #8 Simple syntax example
[ deletia ]
echo "He drank some $juices[koolaid1] juice.".PHP_EOL;
[ deletia ]
Complex (curly) syntax

This isn't called complex because the syntax is complex, but because it allows for the use of complex expressions.
[ deletia ]
// Works, quoted keys only work using the curly brace syntax
echo "This works: {$arr['key']}";

That is, you can reference associative arrays from within a double-quoted string in one of two ways:

 // simple - don't quote your keys
 "... $_GET[version] ..."

// complex - you may quote your keys, but must surround the expression in braces
"... {$_GET['version']} ..."

HOWEVER, you shouldn't be doing either here. You should instead be using a parameterised statement in order to prevent SQL injection attacks:

$stmt = $conn->prepare("SELECT * FROM $table_name WHERE verion = ?");
$stmt->execute([$_GET['version']]);

MySql database query not working when I use _GET inside

5 Answers5

Example #8 Simple syntax example

Complex (curly) syntax