CODEWITHSUNDEEP
phpsqliteapache2.4php-5.6
Cesare
Cesare

Reputation: 1749

How to invoke php url with parameters and using them in SQL query on SQLite3 (quoting string ...)?

I'm on Windows 7, using PHP Version 5.6.14 on Apache 2.4: I've to build a query select on a SQLite3 database using a PHP.

NOTA: I'm a newbye on PHP .....

My code is the follow

<?php

$comune = $_GET["comune"];
echo $comune;
echo '<br>';
echo '<br>';

$db = new SQLite3('PrezziBenzina');

if ($db) {
    $q = $db->prepare('SELECT distr.Gestore, distr.Indirizzo, distr.Bandiera, prz.descCarburante, prz.prezzo FROM anagrafica_impianti_attivi as distr join prezzo_alle_8 as prz ON (prz.idImpianto = distr.IdImpianto) WHERE distr.Comune = ?');
    $q->bindvalue(1, $comune, SQLITE3_TEXT);
    $results = $q->execute();

    while ($row = $results->fetchArray(SQLITE3_ASSOC)) {
       print $row['Bandiera'];
       print ' -- ';
       print $row['descCarburante'];
       print ' -- ';
       print $row['prezzo'];
       print '<br>';
    }
} else {
    print "Connection to database failed!\n";
}
?>

When I call my procedure using 

http://localhost/ProvaAccessoDB-V02.php?comune=CARIGNANO

all works fine, but when I call my procedure using

http://localhost/ProvaAccessoDB-V02.php?comune=LA LOGGIA
http://localhost/ProvaAccessoDB-V02.php?comune=L'AQUILA
http://localhost/ProvaAccessoDB-V02.php?comune=SANT'ALBANO STURA
http://localhost/ProvaAccessoDB-V02.php?comune=AGLIE'

my query doesn't work.

How can I quote / unquote my $comune variable to manage all the url that don't work?

Any suggestions is appreciated. Thank you very much in advance

Cesare

Upvotes: 1

Views: 1052

Answers (2)

Mark Ng
Mark Ng

Reputation: 200

Try..

<?php 
//conn parameter 
$db = new PDO('sqlite:PrezziBenzina'); 

//this will set to catch error 
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); 

//get url param 
$comune = $_GET['comune']; 
echo $comune; 
echo '<br>'; 
echo '<br>'; 

//set query var - OP original, nothing wrong, just testing with mine.
//$q="SELECT distr.Gestore, distr.Indirizzo, distr.Bandiera, prz.descCarburante, prz.prezzo 
//FROM anagrafica_impianti_attivi as distr 
//JOIN prezzo_alle_8 as prz ON (prz.idImpianto = distr.IdImpianto) WHERE distr.Comune = :Comune";

//set query var
$q="SELECT anagrafica_impianti_attivi.Gestore, anagrafica_impianti_attivi.Indirizzo, anagrafica_impianti_attivi.Bandiera, prezzo_alle_8.descCarburante, prezzo_alle_8.prezzo 
    FROM anagrafica_impianti_attivi
    INNER JOIN prezzo_alle_8
    ON prezzo_alle_8.idImpianto = anagrafica_impianti_attivi.IdImpianto
    WHERE anagrafica_impianti_attivi.Comune = :Comune";

//as the name suggest, it try to query and if there is error, we cath the error, these are useful during staging.   
try { 
//prepare query
$stmt = $db->prepare($q);

//bind  
$stmt->execute(array(':Comune'=>$comune, )); 

//fetch and print
while ($row = $stmt->fetch(SQLITE3_ASSOC)){
    print $row['Bandiera']; 
    print ' -- '; 
    print $row['descCarburante']; 
    print ' -- '; 
    print $row['prezzo']; 
    print '<br>'; 
    } 
}

//catch error 
catch(PDOException $e) { 
    print "Something went wrong or Connection to database failed! ".$e->getMessage();
} 
?>

Have fun. also, you can't pass yoururl.php?comune=LA LOGGIA, use LA%LOGGIA in html or use POST method instead.

Upvotes: 1

Samir Selia
Samir Selia

Reputation: 7065

Escape the string using escapeString().

$q->bindvalue(1, $db->escapeString($comune), SQLITE3_TEXT);

Upvotes: 0

Related Questions