Using text values in query

Question

It seems that I am an idiot, trying to perform a simple query to a SQL database in C#. This is the query, I am trying to execute:

 _query = "SELECT PC.SN, User.Name + ' ' + User.Family as AssignedTo " +  
          "FROM PC LEFT JOIN Users ON PC.USERID = Users.ID " + 
          "WHERE PC.Type = '" + AssetTypeCB.SelectedItem.ToString() + "'";

The problem is that I am getting a "cannot call methods on nvarchar" error message. Do you have any idea what might be the issue?

See http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection for why the above comments are good ones. — Daniel Kelley, Jan 24 '13 at 11:08

score 7 · Accepted Answer · edited May 23 '17 at 12:01

7

Your query seems wrong. You need to change User.Name to Users.Name, et al. The correct query would be:

 _query = "SELECT PC.SN, Users.Name + ' ' + Users.Family as AssignedTo " +
          "FROM PC LEFT JOIN Users ON PC.USERID = Users.ID " + 
          "WHERE PC.Type = '" + AssetTypeCB.SelectedItem.ToString() + "'";

Also, allow me to suggest using parameterized queries for your code. This can tell you why you should.

edited May 23 '17 at 12:01

Community

1
1

answered Jan 24 '13 at 11:05

Ioannis Karadimas

7,746
3
35
45

Thank you very much for your help and guidance. It is working now :) – user2007175 Jan 24 '13 at 11:15

score 0 · Answer 2 · answered Jan 24 '13 at 11:08

I think it is better to add parameters to the query

using System;
using System.Data;
using System.Data.SqlClient;

class ParamDemo
{
    static void Main()
    {
        // conn and reader declared outside try
        // block for visibility in finally block
        SqlConnection conn   = null;
        SqlDataReader reader = null;

        string inputCity = "London";

        try
        {
            // instantiate and open connection
            conn =  new 
                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
            conn.Open();

            // don't ever do this
            // SqlCommand cmd = new SqlCommand(
            // "select * from Customers where city = '" + inputCity + "'";

            // 1. declare command object with parameter
            SqlCommand cmd = new SqlCommand(
                "select * from Customers where city = @City", conn);

            // 2. define parameters used in command object
            SqlParameter param  = new SqlParameter();
            param.ParameterName = "@City";
            param.Value         = inputCity;

            // 3. add new parameter to command object
            cmd.Parameters.Add(param);

            // get data stream
            reader = cmd.ExecuteReader();

            // write each record
            while(reader.Read())
            {
                Console.WriteLine("{0}, {1}", 
                    reader["CompanyName"], 
                    reader["ContactName"]);
            }
        }
        finally
        {
            // close reader
            if (reader != null)
            {
                reader.Close();
            }

            // close connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

nerdybeardo · Answer 3 · 2013-01-24T11:26:25.590

This query exposes you to a SQL Injection attack. Yous hould convert it to a prepared statement, however the actual error is from your use of the table name Users. You have SELECT ... User which should be Users, the reason you're not getting a wrong column name error is because User is a reserved keyword and therefore SQL Server will give you that particular error unless you delimit the table name with []. The following code should fix it.

string _query = "SELECT PC.SN, Users.Name + ' ' + Users.Family as AssignedTo FROM PC LEFT JOIN Users ON PC.USERID = Users.ID WHERE PC.Type = @Type";

SqlConnection conn = new SqlConnection("YOUR_CONNECTION_STRING");
SqlCommand cmd = new SqlCommand(_query, connection);
cmd.Parameters.AddWithValue("Type", AssetTypeCB.SelectedItem.Value);
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);

If your table name is actually User then this code will fix the error

string _query = "SELECT PC.SN, [User].Name + ' ' + [User].Family as AssignedTo FROM PC LEFT JOIN [User] ON PC.USERID = [User].ID WHERE PC.Type = @Type";

SqlConnection conn = new SqlConnection("YOUR_CONNECTION_STRING");
SqlCommand cmd = new SqlCommand(_query, connection);
cmd.Parameters.AddWithValue("Type", AssetTypeCB.SelectedItem.ToString());
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);

More on the SQL Injection attack

Suppose that smooene entered in your original statement the following value.

'; DELETE FROM Users; --

When you execute your SQL Statement, SQL Server will interpret it as the following:

SELECT PC.SN, User.Name + ' ' + User.Family as AssignedTo  
FROM PC LEFT JOIN Users ON PC.USERID = Users.ID 
WHERE PC.Type = ''; DELETE FROM Users; --'

This statement is completely valid and given the level of permissions your user has this can possibly delete all records from the Users table. Using prepared statements help to stop that from happening.

Using text values in query

3 Answers3