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