PHP & SQL: Best way to protect this query from SQL injections NOT using PDO

Question

Hi,

I have a query which is run against a mssql database and I'm not using PDO drivers. Is there something like prepared statement i can use?

Here is the query:

$tsql = "INSERT INTO cplinktable (liferayid, bmsid, autotaskid, waspdb, cpid) VALUES ($liferayid, $bmsid, $autotaskid, '$waspdb', $cpid)";

thanks,

Jonesy

Answer 1

You should at least escape the values.

PHP Manual - mysql_real_escape_string

Answer 2

Try Prepare Statements with sprint()

$tsql = "INSERT INTO cplinktable (liferayid, bmsid, autotaskid, waspdb, cpid) VALUES (%d, %d, %d, '%s', %d)";

$tsql = sprintf($tsql, $liferayid, $bmsid, $autotaskid, $waspdb, $cpid);
echo $tsql; // you would execute this but printing to the screen to show the query

Answer 3

$query = sprintf("INSERT INTO cplinktable (liferayid, bmsid, autotaskid, waspdb, cpid) VALUES ('%s','%s','%s','%s','%s')",
            mysql_real_escape_string($liferavid),
            mysql_real_escape_string($bmsid),
            mysql_real_escape_string($autotaskid),
            mysql_real_escape_string($waspdb),
            mysql_real_escape_string($cpid));

Answer 4

its as simple as useing mysql_real_escape on strings and typecasting on digits / ints / doubles

(int)$number; //Safe
(double)$double; //Safe
mysql_real_escape_string($string); //Safe

This used on every piece of data you insert into your database will be safe

Answer 5

does that help?

http://www.php.net/manual/en/function.mssql-query.php#95967

Answer 6

The Microsoft SQL Driver for PHP provides prepared statements:

• http://www.microsoft.com/downloads/en/details.aspx?FamilyID=80e44913-24b4-4113-8807-caae6cf2ca05&displaylang=en
• http://msdn.microsoft.com/en-US/library/cc296184%28v=SQL.90%29.aspx