Simple SQL Syntax Issue

Question

My query does not seem to be responding in my code, however, in the SQL scripting area my script executes cleanly. The issue at hand must just be the way that I am inserting the variable from the rest of the query such as adding the parenthesis on the end but doesn't get read. I'm doing this in a .NET Gridview. Any ideas would be appreciated. Please let me know if you need more information.

SQL (no issues):

SELECT [TeamID], [TeamName], [SportsType], [ContactName], [ContactPhone], [ContactEmail] FROM [Teams] WHERE CompanyID = (SELECT CompanyID FROM Company WHERE companyadminUserName = 'rec1') OR CompanyID = (SELECT CompanyID FROM Employee WHERE EmployeeBarcodeNumber = 'rec1')

In Code (issues):

public String loggedInUser = "rec1";

SqlDataSource1.SelectCommand = "SELECT [TeamID], [TeamName], [SportsType], [ContactName], [ContactPhone], [ContactEmail] FROM [Teams] WHERE CompanyID = (SELECT CompanyID FROM Company WHERE companyadminUserName ="+loggedInUser+") OR CompanyID = (SELECT CompanyID FROM Employee WHERE EmployeeBarcodeNumber ="+loggedInUser+"')'";

don't use string concat - use a parameterized query – Daniel A. White Jun 17 '15 at 01:38 — Daniel A. White, Jun 17 '15 at 01:38
You're missing single quotes around loggedInUser – Mr Moose Jun 17 '15 at 01:39 — Mr Moose, Jun 17 '15 at 01:39

score 4 · Answer 1 · edited May 23 '17 at 11:43

It is potentially a very bad security hole to concatenate queries like this. If you are writing production code, use SqlParameters to prevent sql injection attack. Using SqlParameters also means you have less chance of hitting quotation related bugs (such as the one you have).

How does SQLParameter prevent SQL Injection?

SqlCommand sqlCmd = 
@"SELECT 
    [TeamID], 
    [TeamName], 
    [SportsType], 
    [ContactName], 
    [ContactPhone], 
    [ContactEmail] 
FROM [Teams] 
WHERE CompanyID = (
    SELECT CompanyID 
    FROM Company 
    WHERE companyadminUserName = @loggedInUser
) 
OR CompanyID = (
    SELECT CompanyID 
    FROM Employee 
    WHERE EmployeeBarcodeNumber = @loggedInUser
)";

sqlCmd.Parameters.Add(new SqlParameter("@loggedInUser", SqlDbType.NVarChar) { Value = loggedInUser });

score 1 · Accepted Answer · answered Jun 17 '15 at 01:38

You seem to be missing some " ' " when building the query in your code.

Try this one:

public String loggedInUser = "rec1";

SqlDataSource1.SelectCommand = "SELECT [TeamID], [TeamName], [SportsType], [ContactName], [ContactPhone], [ContactEmail] FROM [Teams] WHERE CompanyID = (SELECT CompanyID FROM Company WHERE companyadminUserName = '"+loggedInUser+"') OR CompanyID = (SELECT CompanyID FROM Employee WHERE EmployeeBarcodeNumber = '"+loggedInUser+"')";

score 0 · Answer 3 · answered Jun 17 '15 at 01:39

your sql command string something wrong

SqlDataSource1.SelectCommand = "SELECT [TeamID], [TeamName], [SportsType], [ContactName], [ContactPhone], [ContactEmail] FROM [Teams] WHERE CompanyID = (SELECT CompanyID FROM Company WHERE companyadminUserName = '"+loggedInUser+"') OR CompanyID = (SELECT CompanyID FROM Employee WHERE EmployeeBarcodeNumber = '"+loggedInUser+"')";

u should add '' in the loggedInUser

Simple SQL Syntax Issue

3 Answers3