How can I avoid SQL injection on this query

[sql-lover]

Trying to make my code more secure.

 

This is what I currently have, which is not secure by any means:

 

$query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME='$table'";
  
// Run PRO query

$qresult1 = sqlsrv_query($dbPRO, $query1);
if ($qresult1 === false) {
    exitWithSQLError('Retrieving schema failed.');
}

 

This is how I changed it,

 

$query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME=?";
$params = array(1, $table);	 

// Run PRO query
$qresult1 = sqlsrv_query($dbPRO, $query1, $params);
if ($qresult1 === false) {
    exitWithSQLError('Retrieving schema failed.');
}

 

but I'm getting this error:

 

SQL-Status: 22018
Code: 245
Message: [Microsoft][sql Server Native Client 10.0][sql Server]Conversion failed when converting the nvarchar value 'sysrscols' to data type int

 

Please notice I am using sqlsrv_query function because my database engine is MS-SQL 2008. That's why I'm a bit confused. Most documentation online is pointed to MySQL.

 

exitWithSQLError is a customized function of mine, so please ignore. 

 

Any help or hints is appreciated,

 

Thanks,

[kicken]

The documentation for the sqlsrv_* functions is on microsoft's site at http://msdn.microsoft.com/en-us/library/cc296152.aspx.

 

The params parameter should be an array of values to put in the placeholders.  You only have one placeholder but your given it two values for params.  It's subbing in the value 1 for your placeholder which is an int.  As a result it is trying to cast the column value to an int to do the comparison but it cant so you get the error.

 

You want to only pass one value which is your $table variable.

 

[sql-lover]

The documentation for the sqlsrv_* functions is on microsoft's site at http://msdn.microsoft.com/en-us/library/cc296152.aspx.

 

The params parameter should be an array of values to put in the placeholders.  You only have one placeholder but your given it two values for params.

 

 

Thanks, fixed .... this way ...

 

$params = array($table);

 

Very silly mistake, but thanks again for pointing that out!