John Davidson

Message:

a have an sqlite table

CREATE TABLE "lib" (

    "id"    INTEGER UNIQUE,

    "addr"  TEXT UNIQUE,

    "data"  TEXT,

    PRIMARY KEY("id")

)

testing dataset contains:

...

1   arara       arararar test

2   unit=comp   comp test

...

I use code next to test requests

<? $db = new PDO('sqlite:main.db') or die('Unable to open database');



echo ("qry: ".$_SERVER["QUERY_STRING"]."<br>");

foreach ($db->query("SELECT * FROM lib WHERE addr='".$_SERVER["QUERY_STRING"]."'", PDO::FETCH_ASSOC/*_NUM*/) as $row) {

    //echo($row[0].'<br>');

    echo($row['addr'].'<br>');

    echo($row['data'].'<br>');

}



$db = null; ?>

so, when I do script.php?arara it returns

qry: arara

arara

arararar test

but, when I do script.php?unit=comp it returns no data (just QUERY_STRING)

qry: unit=comp

what wrong with my code?

upd:

1. this question is not about security


2. php modified for PDO prepare, now its return no data with any request

<? $db = new PDO('sqlite:main.db') or die('Unable to open database');



echo ("qry: ".$_SERVER["QUERY_STRING"]."<br>");



$qry=$db->prepare("SELECT * FROM lib WHERE addr='?'");

$qry->execute(array($_SERVER["QUERY_STRING"]));

foreach ($qry as $row) {

//foreach ($db->query("SELECT * FROM lib WHERE addr='".$_SERVER["QUERY_STRING"]."'", PDO::FETCH_ASSOC/*_NUM*/) as $row) {

    //echo($row[0].'<br>');

    echo($row['addr'].'<br>');

    echo($row['data'].'<br>');

}



$db = null; ?>

---

My Answer:
The issue in your code is with the way you are preparing the query with a placeholder for the parameter. In PDO, placeholders should be represented by a question mark (?) without quotes around it.

Here is the corrected code:

php
$db = new PDO('sqlite:main.db') or die('Unable to open database');

echo ("qry: ".$_SERVER["QUERY_STRING"]."
");
$qry = $db->prepare("SELECT * FROM lib WHERE addr=?");
$qry->execute(array($_SERVER["QUERY_STRING"]));
foreach ($qry as $row) {
echo($row['addr'].'
');
echo($row['data'].'
');
}

$db = null;
?>


By using the correct placeholder syntax, the query should now return data for any request, including `script.php?unit=comp`.