PHP and MySQL Basics IV -- SQL Injection and Prepared Statements
SQL Injection is a highly feared and often misunderstood problem. The basic phobia is that someone hijacks your SQL request and suddenly has full access to everything in your database. Well, it usually is not that easy and it is actually easy to avoid.
The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this.
Rule 1: Never Trust User Supplied
The usual example is something like a query SELECT * FROM customer_data WHERE customer_id='$id' and the programmer was expecting an integer for the customer_id. But a dastardly use inserts some horrible SQL code to pirate the information so the query looks like SELECT * FROM customer_data WHERE customer_id=1 OR customer_id > 0 and suddenly all your customer data is out free in the universe waiting for who knows what.The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this.
if is_int($customer_id) {
//Do all the stuff we want to do if we have a integer
//submitted for a customer_id
} else {
echo "Hey! I want an INTEGER for a customer identification number!";
}
Even more dastardly and from the PHP Manual where the dastardly injector resets all the passwords: <?php But a malicious user subits the value ' or uid like'%admin% to $uid to change the admin's password, or simply sets $pwd to hehehe', trusted=100, admin='yes to gain more privileges. Then, the query will be twisted: >?php // $uid: ' or uid like '%admin% $query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like '%admin%';" // $pwd: hehehe', trusted=100, admin='yes $query = "UPDATE usertable SET pwd='hehehe', trusted=100, admin='yes' WHERE ...;";?>Paranoid yet? This is another case checking the values for reasonableness can save grief. Is the uid an integer, is that integer in a proper range for uids? If too low or too high, you need to suspect someone is doing something bad.
On the database side
One thing I have recommended and implemented for years is separate users for SELECTs and INSERT/UPDATE/DELETEs. It is too easy to use one connection string over and over. But if you are in a situation where you need to take 'loose' information from an user and use that information against you data base, you need to split the queries. Set up a cust_read account on the MySQL server that can read customer data ONLY. The a corresponding cust_mod for all the other queries. This way you are assured that they can not delete or update records with that account if things get compromised. Also do not user superuser accounts like root for production database interactions -- save these accounts for maintenance functions.Bound variables
The MySQL server supports using anonymous, positional placeholder with ?. Again from the PHP Manual:
/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
Or as used in a SELECT
/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("SELECT * FROM test WHERE id = ?"))) {
echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
} else {
echo "Got it!!!\n";
}
Nhận xét
Đăng nhận xét