MSSQL Parameterized Queries

Question

Over MySQLi I would do:

$sql = $connect->prepare("INSERT INTO Table (a, b) VALUES (?, ?)");
$sql->bind_param('ss', $a, $b);
$sql->execute();
$sql->close();

Now the question is, how I do the same thing with mssql for a SQL Server instead of MySQL?

I know I could do:

$sql = sqlsrv_query($connect, "INSERT INTO Table (a, b) VALUES (?, ?)", array($a, $b));

However, this requires the SQL Drivers specific for windows, which I cannot use over a linux platform (where in fact my website is hosted).

My server has however the mssql library installed, and even if it's deprecated, still it work pretty good with PHP 5.6

I have troubles on parameterizing the queries though, all I could do is:

$sql = "INSERT INTO Table (a, b) VALUES ('$a', '$b')";
mssql_query($sql, $connect);

But of course I do not want to directly insert the values, so how I can parameterize that query on mssql?

Answer 1

Seems that this is not of the most easiest things to do. Anyway definitely prepared statements will happen with PDO

From original PHP docs:

If it is not possible to use SqlSrv, you can use the PDO_ODBC driver to connect to Microsoft SQL Server and Sybase databases, as the native Windows DB-LIB is ancient, thread un-safe and no longer supported by Microsoft.

You can read PDO_ODBC on UNIX systems section for your case. Don't forget to read comments too, they might be useful for you to understand how should this happen.

You can refer to this stack overflow question.

And this is how you make parameterized query with PDO:

$sql = 'SELECT name, colour, calories FROM fruit
    WHERE calories < :calories AND colour = :colour';
// where $dbh is PDO object
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
$yellow = $sth->fetchAll();

see other examples on official docs.

Good luck friend and don't forget to share your experience.