Hi there guys,
I have just released alpha 1 of my personal to a select few people and one of them informed me that an sql injection attack is easily done. I know there is mysql_real_escape_string() which I can use, but is there anything else I should be using to stop my application being attacked?
Protecting my application - SQL
Moderator: General Moderators
- social_experiment
- DevNet Master
- Posts: 2793
- Joined: Sun Feb 15, 2009 11:08 am
- Location: .za
Re: Protecting my application - SQL
You've probably heard this but checking input is the best way to avoid getting SQL-injected. Use htmlentities() and check the type of all input received from users and query strings. Even if you pass 'safe' information via a query string it's not a given that it won't be manipulated by malicious users.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Re: Protecting my application - SQL
htmlentities() doesn't really prevent SQL Injection - it's to guard against XSS.social_experiment wrote:You've probably heard this but checking input is the best way to avoid getting SQL-injected. Use htmlentities() and check the type of all input received from users and query strings. Even if you pass 'safe' information via a query string it's not a given that it won't be manipulated by malicious users.
The best way to prevent SQL Injection is to use parametrized queries. The mysql_ library doesn't support this (or a wealth of other things), and has been deprecated for quite a while now (since '05, I believe).
If you wish to use the newer features of both PHP and MySQL, then you have to use the newer methods of communicating with MySQL databases that have been added to the language. Specifically, the 2 options are MySQLi and PDO.
MySQLi stands for MySQL Improved, and is an effort to provide the new features of MySQL to PHP with a new interface to replace the old mysql_ functions. MySQLi has several inherent benefits over the old version. It uses an object-orientated interface, which is certainly beneficial if you're creating a large application. It also supports a procedural style though, so either way it'll tailor to your particular coding style. It supports Prepared Statements within MySQL and multiple statements.
The Second option is PDO (PHP Data Objects), and this is the one that I personally use. PDO only has an Object Orientated interface, which may not suit you if you're creating a site with a procedural style. However, it will work as an abstraction layer, meaning your code will (theoretically) work on any database engine. If you choose to develop for MySQL, Postgre, Firebird, or practically any other database system, PDO will support it, and use exactly the same syntax. Indeed, if you choose to change from MySQL to Postgre midway through your project, then you'll find that if you're just using CRUD database operations it may port exactly, with you only changing a minimum amount of code. This is a very, very powerful tool, especially if you wish to distribute the application.
In addition to this, PDO has the advantage of better iteration support, it throws a specific exception (PDOException) when something goes wrong, and its a compiled C Module for PHP.
The real advantage for you is parametrized queries/prepared statements.
Code: Select all
$dbh = new PDO('mysql:host=localhost;dbname=MyDatabase', "DatabaseUsername", "DatabasePassword"); //Connect to the db
$query = $dbh->prepare("SELECT * FROM users WHERE username = ?");
$query->execute(array($username));
$result = $query->fetchObject();
$password = $result->password;
The first line handles the connection to the database. It's done in Connection String format.
The second line prepares the statement. When you prepare a statement you replace all user supplied variables with question marks. You can also use named parameters as follows
Code: Select all
$query = $dbh->prepare("SELECT * FROM users WHERE username = :username");
$query->execute(array(":username" => $username));
Though you can supply an array of arguments to the execution function, you can also use the bindParam function to bind parameters to the query if you use named parameters.
Code: Select all
$query = $dbh->prepare("SELECT * FROM users WHERE username = :username");
$query->bindParam(":username", $username);
$query->execute();
The benefits of this method are that it is completely, 100%, SQL Injection proof.
Oh, and you can also do cool stuff like this.
Code: Select all
class User {
public $id;
public $username;
public $password;
public $group;
function printInfo() {
echo $username." is part of the group ".$group;
}
}
$stmt = $db->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
foreach ($users as $user)
$person->printInfo();