Dynamic SQL string wildcard % causes 'Invalid Column Name' in Visual Studio

Question

Sproc in SQL Server 2005 parses, compiles and runs successfully via SQL Management Studio. I've recently imported the schema into Visual Studio Database Edition 2008 and attempting to 'build' the project.

The collation on both the database I generated the script from , and the 'temporary' design time database are the same (SQL_Latin1_General_CP1_CI_AS)

Code currently looks like this:

DECLARE @SQL varchar(2000)
IF @Username <> ''
 SET @SQL = @SQL + ' AND Username LIKE ' + "'" + @Username + "%'"

I receive these errors:

 Error  261 TSD4001: Invalid column name '''. (SQL error = 207) 
 Error  262 TSD4001: Invalid column name '%''. (SQL error = 207)

Is there an approved way of using wildcards in dynamic sql generation that won't break the Visual Studio build process?

Thanks

Answer 1

Try replacing the double quotes with the correct number of single quotes.

Answer 2

Good catch by Mitch Wheat.

You need to escape each single quote character desired in the SQL string, by using two of them consecutively. (The first one "closes" the string but the following one tells the parser this is a single quote within the string)

Specifically, you want:

...  ' AND Username LIKE ''' + @Username + '%'''

Answer 3

Are you doing this in C# or VB.Net?

in either case, Replace each single quote with 2 single quotes:

in your client code, if username is a client side variable, where you call this you need

  SQL = SQL + " AND Username LIKE ''" +  username + "%''";  c#
  SQL = SQL + " AND Username LIKE ''" +  username + "%''"   vb.Net

or, if you are using parameters (@UserName)

  SQL = SQL + " AND Username LIKE ''@Username%''";  c#
  SQL = SQL + " AND Username LIKE ''@Username%''"   vb.Net

Answer 4

Thanks for the lightning fast responses everyone, really helpful Mitch and mjv. Now to do a sql injection to prove to colleague that it was bum code!