How to avoid user delete column

Columns can be dropped using ALTER TABLE. But how is your question related to security?

Thanks
Laurentiu

LaurentiuCristofor at 2007-9-9 >

ALTER TABLE <table_name> DROP COLUMN <column_name>

See also: http://msdn2.microsoft.com/en-US/library/ms190273(SQL.90).aspx

That article has an explicit example for this:

B. Dropping a column

CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) ; GO ALTER TABLE doc_exb DROP COLUMN column_b ; GO EXEC sp_help doc_exb ; GO DROP TABLE doc_exb ; GO 

LaurentiuCristofor at 2007-9-9 >

In particular, the statement (select * from table where column = 'delete from table') is not going to drop any column.

But if you are concerned about SQL injection, that would be an issue if it's the user who specifies the column value.

For example, let's say you get the user input in variable @col1_value and then you concatenate that and execute:

select * from t1 where col1 = '@col1_value'

If @col1_value is: val1, the query executed will be:

select * from t1 where col1 = 'val1'

which is fine.

But if @col1_value is something like: val1';alter table t1 drop column col1;--, then the query you will execute will be:

select * from t1 where col1 = 'val1';alter table t1 drop column col1;--'

and this will select, then drop the column, assuming it executes under a context that can do that. This is SQL injection.

To avoid this, you need to properly concatenate the string for the dynamic query. Rather than concatenating the following three pieces:

select * from t1 where col1 = '
@col1_value
'

you should concatenate the following:

select * from t1 where col1 =
quotename(@col1_value, '''')

By using quotename, you will properly quote the column value and this will prevent a malicious user from injecting code by using a specially crafted column name.

Thanks
Laurentiu

LaurentiuCristofor at 2007-9-9 >