Executing Query with Parameters

Executing query with parameters

You could open yourself up to SQL injection attacks here, so best practice is to use parameters:

using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();

    using (SqlTransaction dbTrans = dbConn.BeginTransaction())
    {
        try
        {
            using (SqlCommand dbCommand = new SqlCommand("insert into [DB].[dbo].[User] ( [Id], [AccountId], [FirstName], [LastName], [JobTitle], [PhoneNumber] ) values ( @id, @accountid, @firstname, @lastname, @jobtitle, @phonenumber );", dbConn))
            {
                dbCommand.Transaction = dbTrans;

                dbCommand.Parameters.Add("id", SqlType.VarChar).Value = id;
                dbCommand.Parameters.Add("accountid", SqlType.VarChar).Value = accountId;
                dbCommand.Parameters.Add("firstname", SqlType.VarChar).Value = firstName;
                dbCommand.Parameters.Add("lastname", SqlType.VarChar).Value = lastName;
                dbCommand.Parameters.Add("jobtitle", SqlType.VarChar).Value = jobTitle;
                dbCommand.Parameters.Add("phonenumber", SqlType.VarChar).Value = phoneNumber;

                dbCommand.ExecuteNonQuery();
            }

            dbTrans.Commit();
        }
        catch (SqlException)
        {
            dbTrans.Rollback();

            throw; // bubble up the exception and preserve the stack trace
        }
    }

    dbConn.Close();
}

This is a good article for beginners with ADO.Net

EDIT - Just as a bit of extra info, I've added a transaction to it so if the SQL command fails it will rollback.

How to run query with parameters against a database whose name is in a variable using sp_ExecuteSql

You can use below SQL to create that procedure:

create procedure ProcName  @a_database_name varchar(100),@a_project_name varchar(50)
as
begin

EXEC('USE ' + @a_database_name + ';select PH.project_name, PD.employee_id, E.first_name, E.last_name 
from project_header PH 
inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= '+@a_project_name+'  order by 1, 2;')
end

But it has SQL injection risk- if @a_database_name is provided contains in it ";DROP DATABASE". But if you trust the system that is calling this procedure you are good to go.

Or you might use below query without "use databasename" in it:

create procedure ProcName  @a_database_name varchar(100),@a_project_name varchar(50)
as
begin

EXEC('select PH.project_name, PD.employee_id, E.first_name, E.last_name 
from '+@a_database_name+'.dbo.project_header PH 
inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= '+@a_project_name+'  order by 1, 2;')
end

Query with sp_executesql:

create procedure ProcName  @a_database_name varchar(100),@a_project_name varchar(50)
as
begin
DECLARE  @SqlStatment AS NVARCHAR(1000)
SET @SqlStatment = 'select PH.project_name, PD.employee_id, E.first_name, E.last_name 
from '+@a_database_name+'.dbo.project_header PH 
inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= @a_project_name  order by 1, 2;'

EXECUTE sp_executesql @SqlStatment ,N'@a_project_name varchar',@a_project_name

end

Revised Answer:

create procedure ProcName  @a_database_name varchar(100),@a_project_name varchar(50)
as
begin

declare @sp_executesql as nvarchar(1000)

SELECT @sp_executesql = quotename(@a_database_name) + '.sys.sp_executesql'
EXEC @sp_executesql N'select PH.project_name, PD.employee_id, E.first_name, E.last_name 
from project_header PH inner join project_detail PD on PD.project_id = PH.project_id
inner join employee E on E.employee_id = PD.employee_id
where PH.project_name= @a_project_name  order by 1, 2;',N'@a_project_name nvarchar(100)', @a_project_name;

end

How to get the parametrized SQL query (with parameters applyed) before execute it in Dapper?

What you are asking for does not exist at any time while processing your query.

Even when you execute the query, the parameter values are never substituted directly into the SQL command. The whole point of parameterized queries is so code is code, data is data, and the two never cross. This eliminates any possibility of injection attacks, no matter what new language feature, custom escape character, or unicode weirdness you might have to deal with.

On the server side, instead of this:

SELECT * FROM [table] WHERE ID=1234;

It's more as if you run code like this:

DECLARE @ID int;
SET @ID = LoadParameterValueFromClientQueryObject("ID");
SELECT * FROM [table] WHERE ID= @ID;

You're always dealing with a variable, where the value of the variable is held away from the sql language compiler/optimizer until after the command is already compiled into an execution plan.

Error executing query with parameters in golang with postgres driver

Solved, for postgres the correct use of interpolation query is with $1, $2, $3...

so the correct query would be

INSERT INTO fakeclients (uuid, name, last_name, birth_day, email, city, address, password, cellphone) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)

Recently run queries WITH parameter values

No unfortunately, you'd have to use Profiler for that

Oracle - How to execute a query with parameters?

Oracle SQL Developer should handle variables the same way SQLPlus does, that is with the &.

For example ( in SQLPlus for simplicity):

SQL> select 1 from &tableName;
Enter value for tablename: dual
old   1: select 1 from &tableName
new   1: select 1 from dual

         1
----------
         1

What you can not do is use the parameter as a part of a table name, assuming that Developer "knows" which part is the parameter name and which one is the fixed part.
For example:

SQL> select * from &ParamName_Type;
Enter value for paramname_type:

that is, all the string ParamName_Type wil be interpreted as a variable name and substituited with the value you enter.

Also, consider that this is a client-specific behaviour, not an Oracle DB one; so, the same thing will not work in a different client (Toad for Oracle for example).

Consider that you are trying to use a "parameter" that represents a table name, and you only can do this by the means of some client, because plain SQL does not allow it.
If you need to do such a thing in some piece of code that has to work no matter the client, you need dynamic SQL

If you need something more complex, you may need some dynamic SQL; for example:

SQL> declare
  2      vTableName varchar2(30) := '&table_name';
  3      vSQL       varchar2(100):= 'select 1 from ' || vTableName || 
                                    ' union all select 2 from ' || vTableName;
  4      type tResult is table of number;
  5      vResult    tResult;
  6  begin
  7      execute immediate vSQL bulk collect into vResult;
  8      --
  9      -- do what you need with the result
 10      --
 11      for i in vResult.first .. vResult.last loop
 12          dbms_output.put_line(vResult(i));
 13      end loop;
 14  end;
 15  /
Enter value for table_name: dual
old   2:     vTableName varchar2(30) := '&table_name';
new   2:     vTableName varchar2(30) := 'dual';
1
2

PL/SQL procedure successfully completed.

SQL>

Display full query in statement with parameters

Parameters are not concatenated into the command, they are sent separately to the database. Otherwise there will be no difference between using a parameterized query and using a concatenated one. (see the answer to a similar question here.)

This means that in order to debug your queries you will have to work a little harder then if your sql was concatenated by the vb.net code.

If your database supports stored procedure I recommend you start using them instead of parameterized queries. You will probably gain performance, and it will be easier to debug.

If not, you can copy the query as is to the sql editor, and use one of the debugger options to get the values of the parameters and copy them one by one to the sql editor.

Java SQL problem executing query through CallableStatement with parameter

After a lot of attempts, I've found the solution.

The problem is in your variable cdsbecause it could have white spaces before or after.
Try:

cb.setString(1,cds.strip());

For me it worked.

Error executing query with parameters in golang with mysql driver

When you execute the method like this:

_, err := db.ExecContext(ctx, cmd, args)

you pass args as only one []interface{} argument. Slices are not supported as arguments for ExecContext method, unless its []byte.

You need to use the unpack operator with args:

_, err := db.ExecContext(ctx, cmd, args...)

---

Related Topics