query with a table name that has a space...

[vbnetrookie]

I have a table name in SQL Server 2000 that has a space in it
ex: aim international

I had trouble just in the query analyzer with this..I had to place the
name in brackets [] for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query string. But is gives me this
error:
***************
Line 1: Incorrect syntax near 'AIM international'.
Exception Details: System.Data.SqlClient.SqlException: Line 1:
Incorrect syntax near 'AIM international'.
******************
Here is the string:
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
**********************

And the variable PubName is the string AIM international .
I tried placing it in brackets like in the query analyzer :
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &
"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
*******************

and I get this:
*******************
Invalid object name ''AIIM international''.
Exception Details: System.Data.SqlClient.SqlException: Invalid object
name ''AIIM international''.
*******************

Any idea what I have to do for it to work ??? Can I use table names
with spaces or it's just not a good idea???
Thanks for the help guys!!
JMT

[Martijn Tonies]

Hi,

I have a table name in SQL Server 2000 that has a space in it
ex: aim international

I had trouble just in the query analyzer with this..I had to place the
name in brackets [] for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query string. But is gives me this
error:
***************
Line 1: Incorrect syntax near 'AIM international'.
Exception Details: System.Data.SqlClient.SqlException: Line 1:
Incorrect syntax near 'AIM international'.
******************
Here is the string:
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
**********************

And the variable PubName is the string AIM international .
I tried placing it in brackets like in the query analyzer :
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &
There's an extra single quote there: [' ... & pubname ...
"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
*******************

and I get this:
*******************
Invalid object name ''AIIM international''.
Exception Details: System.Data.SqlClient.SqlException: Invalid object
name ''AIIM international''.
*******************

Any idea what I have to do for it to work ??? Can I use table names
with spaces or it's just not a good idea???

Well, it's not an good idea either :-)
--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com

[vbnetrookie]

Well thanks alot Martijn,

Thoses extra quotes really made a difference. Now it all works!!
here's the new string:
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "

Beautiful!! I love it when things end up working!!
Thanks again!
JMT

[Erland Sommarskog]

vbnetrookie (bi****@hotmail.com) writes:

Well thanks alot Martijn,

Thoses extra quotes really made a difference. Now it all works!!
here's the new string:
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "

And now for title enter the following string:

' DROP TABLE [AIM International] --

As a safety precaution, make sure that you have a backup of your database
available.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[vbnetrookie]

I'm not sure what you mean :
enter the following string for title ???
WHat will this do and why add that line if it all works now???
I'm alaways willing to learn new stuff so i'm all ears!!
JMT

[Erland Sommarskog]

vbnetrookie (bi****@hotmail.com) writes:

I'm not sure what you mean :
enter the following string for title ???
WHat will this do and why add that line if it all works now???
I'm alaways willing to learn new stuff so i'm all ears!!

Presumably title comes from an input field. And it is into this input
field you should enter this string and learn what happens. You may
think you are searching for some data, but in fact you will blow away
your table.

This is something which is called "SQL injection". By entering SQL commands
into an input field, an intruder might be able to do things your database
that you did not intend. This is particular a danger if that input field
in on a web site. The trick is simple: Use an ' to close the string and
also a -- at the end to kill the syntax that comes after the query.

The remedy for this problem is simple: rather than building the complete
SQL statement, you use a parameterised statement:

Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode = @postalcode And Title= @title "

You then add the parameters with .AddParameter which I believe is on
the command object. (I'm not a very frequent ADO .Net programmer, so
I don't remember the details.) I encourage you to look it up. And I
cannot stress enough that this is essential stuff.

As you may note, I did not use a parameter for the table name; This is
because table names cannot be parameterized. Usually if you find the need
to determine the table name dynamically, this is a strong indiciation of
a poor database design.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[christopher.secord]

> now for title enter the following string:

' DROP TABLE [AIM International] --

Well, technically you'd need:
[AIM International]; DROP TABLE [AIM International] --

You have to finish that first SQL statement before you can in inject
new code.

I agree that if the table name is a parameter it indicates that the
database is not normalized, but if you *had* to do it that way then I'd
suggest putting the table names into an array and just passing as input
an index to the array (and of course doing bounds checking on it.)