Is this query safe against SQL injection?

[sql-lover]

Hi,

 

I am using parameterized queries on my code, here's the relevant part

 

$params=$_POST['ITGtable'];

$tsql2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, 
  COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME=?";

/* Execute the statement with the specified parameter value.
Display the returned data if no errors occur. */
$stmt2 = sqlsrv_query( $conn, $tsql2, $params);
if( $stmt2 === false )
{
     echo "Statement 2 could not be executed.\n";
     die( print_r(sqlsrv_errors(), true));
}
else
{
     $qty = sqlsrv_fetch_array( $stmt2);
}

 

Do I really have to sanitize $_POST['ITGtable'] for apostrophe, semicolon, etc, to avoid SQL injection problems? Or just with above code I should be safer (I did not say safe) against SQL injection? And if the answer is "No", what could be the sanitize code of function? I am using sqlsrv and MS-SQL database engine; most of the functions we have for sanitize inputs on MySQL are not available for MS-SQL.

 

Thanks in advance,

[scootstah]

Prepared statements eliminate SQL injection.

[sql-lover]

Prepared statements eliminate SQL injection.

 

Thanks for reply.

 

isn't prepare statement the same as parametrized queries? but anyway, my question is about above code.

 

Do I have to sanitize after using a parameterized query?

[sql-lover]

"blonde moment"

 

You're right. Prepared isn't the same as parameterized. But I would prefer not to redesign the whole code and change to prepared if using a parameter is still safe.

[kicken]

Prepared isn't the same as parameterized.

 

They are essentially the same.  Your just passing the parameters and sql together in a call vs doing it separately by preparing the query first then binding parameters later.

[sql-lover]

Prepared isn't the same as parameterized.

 

They are essentially the same.  Your just passing the parameters and sql together in a call vs doing it separately by preparing the query first then binding parameters later.

 

I guess that my question is still there :-)

 

Do I have to sanitize the input? And if answer is Yes, which function or function will help me now than I'm using the Microsoft connector or driver.

[GingerRobot]

The use of a prepared statement or parameterized query will protect you from SQL injection*. Of course, that doesn't mean that you definitely don't require any other form of input validation. You should still check that the query being executed contains expected values. For example, you would still need to check a value is positive, within some range etc, etc.

 

In other words, the use of parameters or prepared statements avoids the need for separate sanitation (e.g. with mysql_real_escape_string() ), but you shouldn't just forget about validation entirely.

 

*Assuming it has been implemented properly

[scootstah]

Prepared isn't the same as parameterized.

 

They are essentially the same.  Your just passing the parameters and sql together in a call vs doing it separately by preparing the query first then binding parameters later.

 

I guess that my question is still there :-)

 

Do I have to sanitize the input? And if answer is Yes, which function or function will help me now than I'm using the Microsoft connector or driver.

 

Not for SQL injection.

[sql-lover]

[quote author=scootstah

Not for SQL injection.

 

Why, can you show (with my own code, of course)? not challenging your comment, but I want to fully understand your explanation.

 

[sql-lover]

The use of a prepared statement or parameterized query will protect you from SQL injection*. Of course, that doesn't mean that you definitely don't require any other form of input validation. You should still check that the query being executed contains expected values. For example, you would still need to check a value is positive, within some range etc, etc.

 

In other words, the use of parameters or prepared statements avoids the need for separate sanitation (e.g. with mysql_real_escape_string() ), but you shouldn't just forget about validation entirely.

 

*Assuming it has been implemented properly

 

Thanks.

 

Since I posted this question I read additional material. Your comment makes sense.