PHP

MySQL och PHP - Hur du kan förhindra SQL-injicering

I exemplen nedan har data skickats till php-sidan från inmatningsfält i ett formulär. $_POST["inmatningsfältnamn"] fångar upp data skickad från formuläret.

Mysqli - Procedural stil

Spar exempelkoden nedan i en fil med filändelsen .php

Exempelkod - mysqli_real_escape_string:

Läs från MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$conn = mysqli_connect($host, $user, $password, $database, 3306);

//check connection
if(mysqli_connect_errno()){
echo "Connect failed";
exit();
}

echo "Connected to database";

// escape input
$input = $_POST["city"];
$city = mysqli_real_escape_string($conn, $input);
echo '<p>'.$city.'</p>'."\n";

// execute query
$query = "SELECT FirstName FROM users WHERE city = '$city'";
$result = mysqli_query($conn, $query);

// fetch values
while($list = mysqli_fetch_assoc($result)){
echo $list["FirstName"].'<br/>'."\n";
}

// close connection
mysqli_close($conn);
?>

Skriv till MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$conn = mysqli_connect($host, $user, $password, $database, 3306);

//check connection
if(mysqli_connect_errno()){
echo "Connect failed";
exit();
}

echo "Connected to database";

// escape input
$firstname = mysqli_real_escape_string($conn, $_POST["firstname"]);
$lastname = mysqli_real_escape_string($conn, $_POST["lastname"]);
$city = mysqli_real_escape_string($conn, $_POST["city"]);

// execute query
$query = "INSERT INTO users (FirstName, LastName, city) VALUES ('$firstname', '$lastname', '$city')";
mysqli_query($conn, $query);

echo "<p>Data saved</p>";

// close connection
mysqli_close($conn);
?>

Exempelkod - mysqli_stmt_bind_param:

Läs från MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$conn = mysqli_connect($host, $user, $password, $database, 3306);

//check connection
if(mysqli_connect_errno()){
echo "Connect failed";
exit();
}

echo "Connected to database";

$stmt = mysqli_prepare($conn, "SELECT FirstName FROM users WHERE city = ?");
mysqli_stmt_bind_param($stmt, 's', $city);
$city = $_POST["city"];

// execute prepared statement
mysqli_stmt_execute($stmt);

// bind variables to prepared statement 
mysqli_stmt_bind_result($stmt, $col1);

// fetch values
while(mysqli_stmt_fetch($stmt)){
 printf("%s<br/>\n", $col1);
}

// close statement
mysqli_stmt_close($stmt);

// close connection
mysqli_close($conn);
?>

Skriv till MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$conn = mysqli_connect($host, $user, $password, $database, 3306);

//check connection
if(mysqli_connect_errno()){
echo "Connect failed";
exit();
}

echo "Connected to database";

$stmt = mysqli_prepare($conn, "INSERT INTO users (FirstName, LastName, city) VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $city);

$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$city = $_POST["city"];

// execute prepared statement
mysqli_stmt_execute($stmt);

// close statement
mysqli_stmt_close($stmt);

echo "<p>Data saved</p>";

// close connection
mysqli_close($conn);
?>

Mysqli - Objekt-orienterad stil

Spar exempelkoden nedan i en fil med filändelsen .php

Exempelkod - mysqli::real_escape_string:

Läs från MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$mysqli = new mysqli($host, $user, $password, $database);

// check connection
if($mysqli->connect_error){
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
echo "Connected to database";
}

// escape input
$city = $mysqli->real_escape_string($_POST["city"]);

$query = "SELECT FirstName FROM users WHERE city = '$city'";

if($result = $mysqli->query($query)){
while($list = $result->fetch_assoc()){
echo $list["FirstName"].'<br/>'."\n";
}
}

// close connection
$mysqli->close();
?>

Skriv till MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$mysqli = new mysqli($host, $user, $password, $database);

// check connection
if($mysqli->connect_error){
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
echo "Connected to database";
}

$firstname = $mysqli->real_escape_string($_POST["firstname"]);
$lastname = $mysqli->real_escape_string($_POST["lastname"]);
$city = $mysqli->real_escape_string($_POST["city"]);

$query = "INSERT INTO users (FirstName, LastName, city) VALUES ('$firstname', '$lastname', '$city')";
$mysqli->query($query);

echo "<p>Data saved</p>";

// Close the connection
$mysqli->close();
?>

Exempelkod - mysqli_stmt::bind_param:

Läs från MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$mysqli = new mysqli($host, $user, $password, $database);

// check connection
if($mysqli->connect_error){
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
echo "Connected to database";
}

if($stmt = $mysqli->prepare("SELECT FirstName FROM users WHERE city = ?")){

// Bind parameters
$stmt->bind_param('s', $city);
$city = $_POST["city"];

//Execute it
$stmt->execute();

//Bind results
$stmt->bind_result($result);

// fetch values
while($stmt->fetch()){
 printf("%s<br/>\n", $result);
}

//close statement
$stmt->close();
}

// close connection
$mysqli->close();
?>

Skriv till MySQL-databas:

<?php
// connect
$host = "127.0.0.1";
$user = "db_username";
$password = "password for db_username"; 
$database = "db_name";

$mysqli = new mysqli($host, $user, $password, $database);

// check connection
if($mysqli->connect_error){
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
echo "Connected to database";
}

if($stmt = $mysqli->prepare("INSERT INTO users (FirstName, LastName, city) VALUES (?, ?, ?)")){

// Bind parameters
$stmt->bind_param('sss', $firstname, $lastname, $city);

$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$city = $_POST["city"];

//Execute it
$stmt->execute();

//close statement
$stmt->close();
}

echo "<p>Data saved</p>";

// close connection
$mysqli->close();
?>

Mer info:
Vanliga säkerhetshål och lösningar
How can I prevent SQL injection in PHP?
mysqli::real_escape_string / mysqli::escape_string /mysqli_real_escape_string
mysqli_stmt::bind_param / mysqli_stmt_bind_param

Till toppen