Is this sufficient to prevent query injection while using SQL Server?

Question

I have recently taken on a project in which I need to integrate with PHP/SQL Server. I am looking for the quickest and easiest function to prevent SQL injection on SQL Server as I prefer MySQL and do not anticipate many more SQL Server related projects.

Is this function sufficient?

$someVal = mssql_escape($_POST['someVal']);

$query = "INSERT INTO tblName SET field = $someVal";

mssql_execute($query);

function mssql_escape($str) {
    return str_replace("'", "''", $str);
}

If not, what additional steps should I take?

---

EDIT: I am running on a Linux server - sqlsrv_query() only works if your hosting environment is windows

Answer 1

The best option: do not use SQL statements that get concatenated together - use parametrized queries.

E.g. do not create something like

string stmt = "INSERT INTO dbo.MyTable(field1,field2) VALUES(" + value1 + ", " + value2 + ")"

or something like that and then try to "sanitize" it by replacing single quotes or something - you'll never catch everything, someone will always find a way around your "safe guarding".

Instead, use:

string stmt = "INSERT INTO dbo.MyTable(field1,field2) VALUES(@value1, @value2)";

and then set the parameter values before executing this INSERT statement. This is really the only reliable way to avoid SQL injection - use it!

UPDATE: how to use parametrized queries from PHP - I found something here - does that help at all?

$tsql = "INSERT INTO DateTimeTable (myDate, myTime,
                                    myDateTimeOffset, myDatetime2)
         VALUES (?, ?, ?, ?)";

$params = array(
            date("Y-m-d"), // Current date in Y-m-d format.
            "15:30:41.987", // Time as a string.
            date("c"), // Current date in ISO 8601 format.
            date("Y-m-d H:i:s.u") // Current date and time.
          );

$stmt = sqlsrv_query($conn, $tsql, $params);

So it seems you can't use "named" parameters like @value1, @value2, but instead you just use question marks ? for each parameter, and you basically just create a parameter array which you then pass into the query.

This article Accessing SQL Server Databases with PHP might also help - it has a similar sample of how to insert data using the parametrized queries.

UPDATE: after you've revealed that you're on Linux, this approach doesn't work anymore. Instead, you need to use an alternate library in PHP to call a database - something like PDO.

PDO should work both on any *nix type operating system, and against all sorts of databases, including SQL Server, and it supports parametrized queries, too:

$db = new PDO('your-connection-string-here');
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
$stmt->bindParam(':username', $user);
$stmt->bindParam(':password', $pass);
$stmt->execute();