2 Replies - 1911 Views - Last Post: 23 April 2012 - 10:15 AM Rate Topic: -----

#1 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

CREATE TABLE dynamic with parameters

Posted 21 April 2012 - 08:15 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: CREATE TABLE dynamic with parameters

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9572
  • View blog
  • Posts: 36,244
  • Joined: 12-June 08

Re: CREATE TABLE dynamic with parameters

Posted 21 April 2012 - 08:26 AM

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
Was This Post Helpful? 2
  • +
  • -

#3 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: CREATE TABLE dynamic with parameters

Posted 23 April 2012 - 10:15 AM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1