stop sqli

[newbi3]

I am working on a site and using $_GET to receive a value to check for in a mysql database. How can I stop this from being injectable? I've spent two days on using mysqli to sanitize the variable but when I try to get my results I either get nothing or an error. I've been using php site for documentation and even copied word for word what they have and made a database containing exactly what is in their example. I just get errors returned saying "This page in under maintenance...". Here is my code:

<?php
    $postNum = $_GET['id'];
    echo "<p>" . $postNum . "</p>";
    //include "login.php";
    $mysqli = new mysqli("127.0.0.1", "user", "passowrd", "database");
    $query = 'SELECT * FROM posts WHERE number = ?';
    $stmt = $mysqli->stmt_init();
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param('i', $postNum);
    $stmt->execute();
    $result = $stmt->get_result();
    echo $result;
    //$row = mysqli_fetch_array($result);
    $row = $result->fetch_array(MYSQLI_NUM);
    echo "<p>" . $row['body'] . "</p>";
  ?>

I am running php version 5.3.10-1ubuntu3.6 on my home Ubuntu server. In my database I have 5 rows: number, date, title, body, author. In my code up there I am trying to return the value for "body" in the row that number=x.

What am I doing wrong here?

[pasteeywhitecoder]

$stmt->bind_param('i', $postNum);

Should be:

$stmt->bind_param(1, $postNum);

[newbi3]

$stmt->bind_param('i', $postNum);

Should be:

$stmt->bind_param(1, $postNum);

Thanks but it didn't work. From what I've read "i" is for an integer value correct?

[digip]

Where is the HTML form that does the GET request? Are you sanitizing user input?

Mainly, you need to sanitize the query, and filter it so it only accepts what you want it to allow called back from the database, because someone can append the end of the URL(since this is a GET request, the URL will be an easy way to do sqli.) on the site or a form request and add other commands like dropping your tables, etc. POST works same way, but they can't see results of the post without packet sniffing and mitm request tampering of data to change values, repost, etc, since a POST, doesn't return data in the address bar of a browser like a GET would.

This might help, but I've done more of the sqli stuff from examples on how to dump databases and attack mostly, and not done much on the sanitation side really. I rely on my programmer for that (bwall, you the man!)

http://www.unixwiz.net/techtips/sql-injection.html#miti

I'm not much of a programmer either, but looks like your query is wrong too, since you aren't even checking if you are connected to the database. This might help in some ways:

http://php.net/manual/en/mysqli.query.php

http://us3.php.net/manual/en/class.mysqli.php

ie:

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

If that code I posted above(insert after the connect statement) returns an error, you aren't connected and it won't bother even trying the rest of the query since it would be a waste of time.

<form method="get" action="pagename.php">
What "id" would you like to query for? <input type="text" value="" name="id" /><br />
<input type="submit" value="Submit" />
</form>

and in the php try:

<?php
// This would be the php file pagename.php that the above form HTTP GET Requests
if(isset($_GET['id'])) {
		$postNum = htmlspecialchars(($_GET['id']),ENT_QUOTES | ENT_HTML401,"UTF-8");
		echo "<p>" . $postNum . "</p>";
	
    //include "login.php";
	// 	shell command line connect test:	mysql -u db_user_name -p -h dabase.site.com
	//	example	new mysqli("localhost", "my_user", "my_password", "world");
    //	$mysqli = new mysqli("dabase.site.com", "db_user_name", "actual_db_passowrd", "database_name_itself");
	$mysqli = new mysqli("127.0.0.1", "user", "passowrd", "database");
	
	if ($mysqli->connect_errno) {
		printf("Connect failed: %s\n", $mysqli->connect_error);
		exit();
		//$mysqli = new mysqli("127.0.0.1", "user", "passowrd", "database"); //This part might be wrong info on your end and above will show error if so
	}
	
//include "login.php";

	// The rest, I don't know if is correct, since I don't normally do database requests or know much about how to structre the rest of this, or if the syntax is even correct
    $query = 'SELECT * FROM posts WHERE number = ?';
    $stmt = $mysqli->stmt_init();
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param('i', $postNum);
    $stmt->execute();
    $result = $stmt->get_result();
    echo $result;
    //$row = mysqli_fetch_array($result);
    $row = $result->fetch_array(MYSQLI_NUM);
    echo "<p>" . $row['body'] . "</p>";
	
}

?>

Edited April 27, 2013 by digip

[newbi3]

Thanks digip but I had a lot of success using PDO instead of mysqli.