Select Statement not working

chrisguk

In another post I asked of the following code was ok for anti SQL injection. They replied yes but when I tested it, the code will not grab anything from the DB. Is there an error in my code?

// DATABASE FUNCTION //Search database for pages matching the current  

// page and display the title, description and keywords 

$page = isset($_GET['page']) ? $_GET['page'] : 'home'; 


if ($mysqli->connect_errno) { 
    printf("Connect failed: %s\n", $mysqli->connect_error); 
    exit(); 
} 

if($stmt = $mysqli->prepare("SELECT * FROM url WHERE page=? LIMIT 1")) { 

$stmt -> bind_param("s",$page); 

$stmt -> execute(); 

$sql = $stmt; 

if ($sql->num_rows > 0) { 
    while ($row = $sql->fetch_object()) 
    { 
        $title = $row->title; 
        $description = $row->descr; 
        $keywords = $row->keywords; 

} 

} else { 

$title = 'Set Default Title'; 
$description = 'Set Default Description'; 
$keywords = 'Set Default Keywords'; 

    $stmt -> close(); 
    } 
} 
$mysqli -> close();

Weedpacket

Is the page number really a string, as you are claiming when you bind it?
Why [font=monospace]$stmt[/font] and [font=monospace]$sql[/font], since they're identical? (The latter would be the one to discard, since it doesn't contain SQL).
You only close the statement if it did not contain any results.
You have an entire [font=monospace]while[/font] loop even though there would never be more than one result (in fact, because of the test immediately before, the body of the loop runs exactly once, so making it a loop is pointless).
Does statement execution succeed (you're currently not checking)? What error is reported if it doesn't?

chrisguk

I am having real problems with this. I introduced error checking as suggested but no error results are being produced. Any suggestions will be helpful?

bradgrafelman

Can you show us what your code looks like now that you've added additional checks?

chrisguk

Hi and thanks Brad for your interest. Here is the code as it stands now:

$page = isset($_GET['page']) ? $_GET['page'] : 'home';
echo $page;

//$page = $mysqli->real_escape_string($page);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

if ($stmt = $mysqli->prepare("SELECT * FROM url WHERE page= ? LIMIT 1"))
{

$stmt -> bind_param("s",$page);
$stmt -> execute();

}
else
                                        {
                                                echo "ERROR: could not prepare SQL statement.";
                                        }



if ($stmt->num_rows > 0) {
    while($stmt->fetch())
    {
       "title=$title
        descr=$description = 
         keywords=$keywords =";

}

} else {

$title = 'Set Default Title';
$description = 'Set Default Description';
$keywords = 'Set Default Keywords';


}
$stmt -> close();

Derokorian

    while($stmt->fetch())
    {
       "title=$title
        descr=$description = 
         keywords=$keywords =";

}

You're not assigned the return value of $stmt->fetch to anything. This block, if run, will produces 3 undefined variable errors.

Have you verified that SELECT * FROM url WHERE page= 'home' LIMIT 1 returns what you expect by running it directly on the db?

chrisguk

Derokorian;11021897 wrote:
    while($stmt->fetch())
    {
       "title=$title
        descr=$description = 
         keywords=$keywords =";

}
You're not assigned the return value of $stmt->fetch to anything. This block, if run, will produces 3 undefined variable errors.

Have you verified that SELECT * FROM url WHERE page= 'home' LIMIT 1 returns what you expect by running it directly on the db?

Sorry my bad the code I pasted is wrong. See the updated version here:

$page = isset($_GET['page']) ? $_GET['page'] : 'home';
echo $page;

//$page = $mysqli->real_escape_string($page);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

if ($stmt = $mysqli->prepare("SELECT * FROM url WHERE page= ? LIMIT 1"))
{

$stmt -> bind_param("s",$page);
$stmt -> execute();

}
else
                                        {
                                                echo "ERROR: could not prepare SQL statement.";
                                        }



if ($stmt->num_rows > 0) {
    while($stmt->fetch())
    {
        $title =title;
        $description = descr;
        $keywords = keywords;

}

} else {

$title = 'Set Default Title';
$description = 'Set Default Description';
$keywords = 'Set Default Keywords';


}
$stmt -> close();

Weedpacket

        $title =title;
        $description = descr;
        $keywords = keywords;

This will generate three undefined constant notices (unless you really do have constants defined named [font=monospace]title[/font], [font=descr]title[/font], and [font=monospace]keywords[/font]), and will set the variables [font=monospace]$title[/font], [font=monospace]$description[/font], and [font=monospace]$keywords[/font] to the string values [font=monospace]"title"[/font], [font=monospace]"descr"[/font], and [font=monospace]"keywords"[/font] respectively.

And you still haven't assigned the return value of the [font=monospace]fetch[/font] call to anything.

And the loop is pointless: no more than one row will be returned by the query, and the loop is only reached if there is at least one row returned. So the loop is only reached if it will run exactly once, so why make it a loop?