[CharlieMay]

I know parameters are used to insert user entered values safely but I ran into something yesterday while helping someone in the vb.net section and now I'm going to bring this up here in hopes that someone can shed some better light on the topic.

The problem was to create a table dynamically by allowing the user to specify the table name and specify fields and field types. Now that was a pretty easy task but here is the problem. Since user input was involved in the sql statement, I wanted to use parameters to do it so I took the user input and built the statement using a Dictionary (Of String, String)

The problem is, my statement looked like this
CREATE TABLE @tableName (@field1 VarChar(50), @field2 VarChar(50))

I then added my parameters with their appropriate values just like I would with any other parameterized statement.

When the sql statement was executed I received syntax error on '@tableName'

When I used concatenation of the string values (bypassing parameters altogether), the statement of course worked perfectly.

So I guess the question is this:
Is there a way to allow a user to create a table by specifying this information with a parameter of sorts?

Is this being overly cautious?

I guess I should say, I never tried to inject anything to see what would happen because I was leaving work when I discovered this. But I really don't see why I couldn't all it would take is to enter a table name and complete it with a field and type and then I could put ;DROP TABLE blah -- and I would think I would have control of the statement.

This post has been edited by CharlieMay: 21 April 2012 - 08:29 AM

[modi123_1]

Just to be clear you had a parameter value for "@tableName", right? It might be running into an issue with "create table @<table name> " might be construed as a table variable (as opposed to temp or regular tables) in sql first..

http://msdn.microsof...y/ms175010.aspx
http://databases.asp...e-variable.html

[CharlieMay]

Yes modi123_1 I think that is exactly what is happening. But I don't see a way of safely building a string that creates a table without a stored procedure.

I can create the table with "CREATE TABLE " & TextBox1.Text & "blah blah blah" but I can inject the statement and wreak havoc.

I made an assumption that I could give the statement a parameter at the table name and set its value as I would a regular statement but then your link showed me that the @TableName was a method of defining a Table Variable so I guess I don't even know if what I was trying to achieve is possible without a stored procedure.