Jump to content

How can I avoid SQL injection on this query


sql-lover

Recommended Posts

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.  ;D

 

Any help or hints is appreciated,

 

Thanks,

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

 

;D

 

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

 

$params = array($table);

 

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

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.