Creating MySQL Prepared Statement

Question

I have absolutely zero experience protecting my SQL data. I am trying to prevent injection attacks on my web service by using prepared statements. I've followed several tutorials, but each one I've implemented has killed my PHP script. How could I protect this query?

$value = (integer)$_GET["name"];
$sql = "SELECT `coordinates`, `center` , `content_string` FROM Regions WHERE `id` = {$value}";

$result = $conn->query($sql);

$rows = array();

if ($result->num_rows > 0) {
        // output data of each row
        while($r = mysqli_fetch_assoc($result)) {
        $rows[] = $r;
        }
    }

Here is my attempt:

$value = (integer)$_GET["name"];
$sql = $dbConnection->prepare('SELECT `coordinates`, `center` , `content_string` FROM Regions WHERE `id` = ?');
$sql->bind_param('i', $value);

$sql->execute();

$result = $sql->get_result();

$rows = array();

if ($result->num_rows > 0) {
        // output data of each row
        while($r = mysqli_fetch_assoc($result)) {
        $rows[] = $r;
        }
}

I'm not really sure why this code doesn't work.

Please show your best attempt at implementing a prepared statement so far, so we can help you figure out why it kills your PHP script — BeetleJuice, Jul 30 '17 at 04:51
Check this page for some advice: http://php.net/manual/en/mysqli.prepare.php. If you have any problem, post the errors updating your question. — Sakura Kinomoto, Jul 30 '17 at 04:54
Why are you mixing procedural and object oriented style of mysqli calls? — Sakura Kinomoto, Jul 30 '17 at 04:56
What exactly "doesn't work" with the code? What error (if any) do you see? — BeetleJuice, Jul 30 '17 at 05:02
@BeetleJuice I just get a standard HTTP 500 error which I know isn't useful — taurus, Jul 30 '17 at 05:06
Right on its own that's not helpful. You need to find out where your PHP installation writes its error log (look in `php.ini` for the `error_log` setting) and go through it. — BeetleJuice, Jul 30 '17 at 05:11

score 0 · Answer 1 · edited Aug 01 '17 at 00:39

Prepared statement with MySQLi

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);

// set parameters and execute
$username= "John";
$email = "john@example.com";
$stmt->execute();

$username= "Mary";
$email = "mary@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();

Php tips and tricks. http://www.phptherightway.com/

If you are concerned about security this is the topic that I love very much. What are the best PHP input sanitizing functions?.

score 0 · Answer 2 · answered Jul 30 '17 at 06:14

You will have to bind the result and below is the code - it is going to work please try it. please check if there any syntax issues in my code. otherwise it will work.

$value = (integer)$_GET["name"];
$sql = $dbConnection->prepare('SELECT 'coordinates', 'center' , 'content_string' FROM Regions WHERE `id` = ?');
$sql->bind_param('i', $value);
$sql->execute();
$sql->bind_result($coordinates, $center, $content_string)

while($sql->fetch())
{
   echo $coordinates;
   echo $center;
   echo $content_string; 
}

Unfortunately, this code does not work. I will look at the error log in the morning. — taurus, Jul 30 '17 at 07:25

Creating MySQL Prepared Statement

2 Answers2