Protecting my application - SQL

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
Monotoko
Forum Commoner
Posts: 64
Joined: Fri Oct 26, 2007 4:24 pm

Protecting my application - SQL

Post by Monotoko »

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?
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Protecting my application - SQL

Post by social_experiment »

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
Sephern
Forum Commoner
Posts: 73
Joined: Sun Jan 04, 2009 4:44 pm

Re: Protecting my application - SQL

Post by Sephern »

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.
htmlentities() doesn't really prevent SQL Injection - it's to guard against XSS.

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;
 
That might look like quite a lot to take in, but it's really quite simple.

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));
 
The third line simply executes the SQL query. The way prepared statements work is that you must supply the list of user supplied arguments to replace either the question marks or named placeholders at execution time. This means that you can execute one prepared query multiple times with different values. Indeed, this offers a performance increase to simply executing the query numerous times due to caching.

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();
 
After you've executed the query, you can then retrieve the resultset in numerous ways. You're probably most used to accessing data in an array ($result['username']), but with PDO you can also fetch it as an object ($result->username).

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();
  
Try doing that in mysql_ ;)
Post Reply