Using prepared Statements MySQLi

chrisguk

Hi,

Hopefully this is the final post about the code below: I have post elsewhere in the past in order to bring my understanding up to speed.

Will the following code that I put together help combat SQL INJECTION:

// DATABASE FUNCTION //Search database for pages matching the current 
// page and display the title, description and keywords

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


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

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

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

$stmt -> execute();
$stmt -> fetch();
}

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

$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();

NogDog

As long as all external values used in the SQL are passed in via bindParam(), you should be fine in terms of SQL injection.

chrisguk

NogDog;11021809 wrote:
As long as all external values used in the SQL are passed in via bindParam(), you should be fine in terms of SQL injection.

I think there is a problem with my syntax, it is not pulling anything from the DB?

NogDog

My first guess is that it will work if you comment out this line:

$stmt -> fetch();

You would use fetch() or fetch_object(), but not both.

chrisguk

No that isnt working. This is what I have so far:

// 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();

NogDog

Then it's time to start inserting some debug code (or walking through it with xdebug if you use that) to determine whether the SQL really looks like what you expect, if the $_GET variable you're using has the expected value, and so forth. You may want to make sure you have error_reporting() turned up to at least E_ALL, and check return values from the prepare() and execute() methods to see if they really worked (and if not make use of the mysqli error functions/properties to find out what happened.