I have not written a single in line SQL statement since I learned about SQL Stored Procedures (sprocs). There are two reasons for this. 1.) Code reuse and management! You only need to write the sproc once and with some parameters you have instant code reuse. You don’t have to remember the 20 places where you used this code as it is in it’s own file.  2.) They are inherently immune to SQL injection. Let me first point out that I am talking about static sprocs, not dynamic. I will post later on creating a proper dynamic stored procedure that is effective against SQL injection.

For this article, I will be using PHP and SQL Server 2008. However, these techniques work in just about every database management system (DBMS) and programming environment (.NET, PHP, etc.) Utilizing sprocs in your web application as well as your desktop application is a great way to harden your system against this attack.

Let’s start with the stored procedure. We will create a simple sproc that will query our Contacts table in our database mydb. I have included a screenshot of the table layout. You can use Visual Studio to access your database for the sproc creation, or you can use the SQL Server Management Studio. For this article, we will be using the SQL Server Management Studio.

 

1.) Connect to your database and expand Programmability and Stored Procedures.

2.) Right click on Stored Procedures and select New Stored Procedure

3.) You can use the template provided to alter it for your sproc or just copy and paste the code below for the example.

USE [mydb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetContacts]
    (
    @City varchar(30) = null,
    @State varchar(30) = null
    )
AS
    SELECT    ENTRYID,
            NAME,
            [ADDRESS],
            CITY,
            [STATE],
            ZIP

    FROM    CONTACTS

    WHERE    CONTACTS.CITY=@CITY AND
            CONTACTS.STATE=@STATE

You will notice that there are no string terminators in this query. That is because it’s a static sproc, not a dynamic one. If someone passes 1′ or ‘1’=’1′;exec master.dbo.xp_cmdshell ‘dir’– the SQL will pass the single quotes and everything to the parameter. This prevents the SQL statement from being altered and thus defeats SQL injection.

Connection From PHP

Okay, now to access the sproc from PHP. Again, you can use any other programming environment. You will have to research how to access your sproc using that environment. I have broken this down into 3 separate php files: 1.) db.php, 2.) contacts.php, and 3.) viewcontacts.php. We will store these in a directory on our web server under mydb.

The first file db.php is straight forward. It’s the connection class for php.

db.php

<?php
    require_once($_SERVER['DOCUMENT_ROOT'] . "/mydb/contacts.php");

    class db
    {
        protected $server = '[yourserver][yourinstance]';
        protected $conn;
        protected $connOptions;
        public $contacts;

        function  __construct() {
            try {
                $this->connOptions = array('Database'=>'mydb', 'UID' => '[username]', 'PWD' => '[password]');
                $this->conn = sqlsrv_connect($this->server, $this->connOptions);
                if ($this->conn) {
                    $this->contacts = new contacts($this->conn);
                }
                else {
                    die(print_r(sqlsrv_errors(), true));
                }
            } catch (Exception $e) {
                throw new Exception($e->getMessage);
            }

        }

        public function close() {
            sqlsrv_close($this->conn);
        }

        public function isConnected() {
            if ($this->conn ) {
                return true;
            }
            else {
                return false;
            }
        }

    }

?>

You will need to replace [yourserver], [yourinstance], [username], and [password] with the appropriate text to connect to your SQL Server database. The next file is the contacts.php which is the stored procedure wrapper class.

contacts.php

<?php
    class contacts {
        protected $conn; 
        public $rowCount;

        function __construct($conn) {
            $this->conn = $conn;
        }

        public function getContacts($city, $state) {
            if ($this->conn ) {
                $spquery = "{call GetContacts(?,?)}"; // IMPORTANT: Must put a ? for each parameter you want to assign!
                $params = array(
                    array($city, SQLSRV_PARAM_IN), //@City
                    array($state, SQLSRV_PARAM_IN), //@State
                );

                $rslt = sqlsrv_query($this->conn, $spquery, $params);
                if ($rslt === false) {
                    echo "Error in executing statement.<br />";
                    die(print_r(sqlsrv_errors(), true));
                }
                $this->rowCount = 0;

                // Header Row
                $rtnCsv = "entryid,name,address,city,state,zip<br>";  

                while($row = sqlsrv_fetch_object($rslt)) {
                    $this->rowCount++;
                    // NOTE: The $row->Variable selection is Case sensitive! It must match the casing in the stored procedure!
                    $rtnCsv .= str_replace(",", "", $row->ENTRYID) . "," .
                                  str_replace(",", "", $row->NAME) . "," .
                                  str_replace(",", "", $row->ADDRESS) . ",".
                                  str_replace(",", "", $row->CITY) . ",".
                                  str_replace(",", "", $row->STATE) . "," .
                                  str_replace(",", "", $row->ZIP) . "<br>";
                }
                sqlsrv_free_stmt($rslt);
                return $rtnCsv;
            }
            else {
                echo "Connection Failed";
                die(print_r(sqlsrv_errors(), true));
            }
        }
    }
?>

This class just has the records returning as a comma separated value. You can change this to however you want. The last file is the viewcontacts.php which calls the database and the contacts wrapper.

viewcontacts.php

<?php
    require_once($_SERVER['DOCUMENT_ROOT'] . "/mydb/db.php");

    $db = new db();
    if($db->isConnected()) {
        echo $db->contacts->getContacts('Chicago', 'IL');
    }
    $db->close();
    unset($db);
?>

Open your browser, navigate to http://yoursite.yourdomain/mydb/viewcontacts.php. If all is working well you should get your test data. (You did put some test data in the database right?)

Now let’s test for SQL injection by changing the viewcontacts.php file.

viewcontacts.php

<?php
    require_once($_SERVER['DOCUMENT_ROOT'] . "/mydb/db.php");

    $db = new db();
    if($db->isConnected()) {
        echo $db->contacts->getContacts('1' OR '1'='1';exec master.dbo.xp_cmdshell 'dir'--', 'IL');
        echo $db->contacts->getContacts('1' OR 1=1;exec master.dbo.xp_cmdshell 'dir'--', 'IL');
        echo $db->contacts->getContacts('CHICAGO', '1' OR '1'='1';exec master.dbo.xp_cmdshell 'dir'--');
        echo $db->contacts->getContacts('CHICAGO', '1' OR 1=1;exec master.dbo.xp_cmdshell 'dir'--');
    }
    $db->close();
    unset($db);
?>

And there you have it. You can run some other tests as well to ensure that the sproc is not being escaped by the parameters. This is one of the simplest ways to avoid SQL injection and allows for easier programming with code reuse and management. Trust me, once you go sproc you never go back!

Share →