PHP + SQL Script - Protect against SQL Inject

Question

I'm using unity to pass values to my php script with HTTP GET. I am new to php and just got my script to work, however, I would like to make sure I can protect against SQL Injection. Can someone please look this over and let me know what I need to change in order to protect it?

<?php

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

$userId = $_GET['userId'];

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT rp FROM RP where userID = '$userId'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        //echo "UserID: " . $row["userID"]. " - RP: " . $row["rp"]."<br>";
    echo "RP: " . $row["rp"]."<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — Louis Loudog Trottier, Jun 21 '15 at 19:29
Don't use `query(` use `prepare` and `execute` and pass the value in the execute. See the link above ^. — chris85, Jun 21 '15 at 19:40

score 2 · Answer 1 · edited Jun 21 '15 at 20:06

In this particular case, you could just check if $_GET['userId'] is numeric with is_numeric(). If it's not, trigger error and exit.

But that's just for simple cases like these. You should read this: How can I prevent SQL-injection in PHP?

What you need is called prepared statements, which in theory, as far as I know, should be impossible to break. You will be able to choose between prepared statements in Mysqli and PDO. It has very, very little flexibility, so my suggestion is to go with the PDO, any day of the week.

Just to clarify - your code is completely prone to injection. All what's needed to do is use a Mysql command in query string, and the script will execute it just like if you wrote it.

Boris · Accepted Answer · 2015-06-22T07:14:04.130

1

To prevent sql injections - use prepared statements. Here's the link: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

UPDATED

Example:

$id = 5;

$c = new mysqli($servername, $username, $password, $dbname);
$s = $c->prepare("SELECT * FROM RP WHERE id = ?");
$s->bind_param('s', $id);
$s->execute();
$r = $s->get_result();
$f = $r->fetch_assoc();

echo $f['rp'];

UPDATED

Example #2:

$id = 5;
$rp = "lalala";
$rp2 = "boomboomboom";

$c = new mysqli($servername, $username, $password, $dbname);
$s = $c->prepare("UPDATE RP SET rp = ?, rp2 = ? WHERE userID = ?");
$s->bind_param('sss', $rp, $rp2, $id);
$s->execute();

edited Jun 22 '15 at 07:14

answered Jun 21 '15 at 19:32

Boris

531
5
12

How would I make it if I wanted to pass multiple values in the prepare statement? Like a SQL Update statement with the update amount and the userId. – Robert Heras Jun 21 '15 at 23:58
I updated the answer, but please, follow the link and read all about "prepared statements" and i promise you that most of your questions will disappear. – Boris Jun 22 '15 at 07:16

PHP + SQL Script - Protect against SQL Inject

2 Answers2