Stack Overflow
Stack Overflow

Questions tagged [parameterized-query]

A pre-compiled and optimized SQL statement that can be executed multiple times by changing certain constant values during each execution. Often used to prevent SQL injection.

A parameterized query or prepared statement is a pre-compiled and optimized SQL statement that is in the form of a template where only certain constant values (parameters) can be changed. It can be executed multiple times by changing the parameters during each execution. A parameterized query looks like

SELECT itemName FROM Product WHERE manufactureDate BETWEEN ? AND ?

The ? are the parameters that subsituted with values provided during each execution. In the above examples they are the from date and to date.

The advantages of a parameterized query are

  • No compiling and optiming overhead for the subsequent executions of the statement
  • SQL Injection is not possible as they are sent to and parsed by the database server separately from any parameters
301 questions
114
votes
11 answers

Is it safe to not parameterize an SQL query when the parameter is not a string?

In terms of SQL injection, I completely understand the necessity to parameterize a string parameter; that's one of the oldest tricks in the book. But when can it be justified to not parameterize an SqlCommand? Are any data types considered "safe" to…
johnnyRose
  • 7,310
  • 17
  • 40
  • 61
69
votes
6 answers

How do parameterized queries help against SQL injection?

In both queries 1 and 2, the text from the textbox is inserted into the database. What's the significance of the parameterized query here? Passing txtTagNumber as a query parameter SqlCommand cmd = new SqlCommand("INSERT INTO dbo.Cars "…
sqlchild
  • 8,754
  • 28
  • 105
  • 167
62
votes
4 answers

What is parameterized query?

What is a parameterized query, and what would an example of such a query be in PHP and MySQL?
totalnoobs
  • 657
  • 1
  • 5
  • 6
33
votes
4 answers

Confusion between prepared statement and parameterized query in Python

As far as I understand, prepared statements are (mainly) a database feature that allows you to separate parameters from the code that uses such parameters. Example: PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3,…
r_31415
  • 8,752
  • 17
  • 74
  • 121
21
votes
3 answers

How to insert null value in Database through parameterized query

I have a datetime datatype : dttm Also the database field type is datatime Now I am doing this: if (dttm.HasValue) { cmd.Parameters.AddWithValue("@dtb", dttm); } else { // It should insert null value into database // through…
Django Anonymous
  • 2,987
  • 16
  • 58
  • 106
17
votes
5 answers

How do you specify 'DEFAULT' as a SQL parameter value in ADO.NET?

I have a parameterized SQL query targetted for SQL2005 which is dynamically created in code, so I used the ADO.NET SqlParameter class to add sql parameters to SqlCommand. In the aforementioned SQL I select from a Table Valued Function with has…
Llyle
  • 5,980
  • 6
  • 39
  • 56
12
votes
3 answers

How to test my ad-hoc SQL with parameters in Postgres query window

In Microsoft SQL Server, to test something like this in the query window: select * from Users where LastName = @lastname I can add this before the command: declare @lastname varchar(16) set @lastname = 'Troy' But in PostgreSQL, I cannot find a…
Carol
  • 363
  • 5
  • 16
10
votes
7 answers

ExecuteNonQuery inside loop

I'm trying to insert a database record inside a loop in C#. It works when I hard code the values like this: string query3 = "INSERT INTO furniture (room_id,member_id) VALUES (222,333);"; SqlCommand cmd3 = new SqlCommand(query3,…
Dan
  • 5,836
  • 22
  • 86
  • 140
10
votes
2 answers

Bulk Parameterized Inserts

I'm trying to switch some hard-coded queries to use parameterized inputs, but I've run into a problem: How do you format the input for parameterized bulk inserts? Currently, the code looks like this: $data_insert = "INSERT INTO my_table (field1,…
Zac Howland
  • 15,777
  • 1
  • 26
  • 42
9
votes
3 answers

What happens if I call ParamByName for a parameter that doesn't exist?

I'm very new to Delphi and received the following piece of code (left out some irrelevant parts) for which I'm trying to understand what it does: object SelectCosts: TIBQuery SQL.Strings = ( 'SELECT * FROM costs ' 'WHERE code =…
Maza89
  • 518
  • 4
  • 12
9
votes
2 answers

How to use parameterized query in Excel using column as parameter?

I am trying to develop a spreadsheet that can locate corresponding records in an external data source. So, let's say I have Column A with a list of identity values. I want to develop Column B, which perhaps shows a count of rows in the table with…
Jay Imerman
  • 4,475
  • 6
  • 40
  • 55
8
votes
2 answers

The way PDO parametrized query works

PLEASE READ THE QUESTION CAREFULLY. It is not usual silly "my code doesn't work!!!" question. When I run this code with intended error try { $sth = $dbh->prepare("SELECT id FROM users WHERE name INN(?,?) "); …
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
7
votes
1 answer

Python sqlite3 parameterized drop table

I have a problem with dropping sqlite3 table in python. I am using standard sqlite3 module. self.conn = sqlite3.connect(...) sql = """ drop table ? """ self.conn.execute( sql, (u'table_name',) ) gives me OperationalError: near "?": syntax error…
pajton
  • 15,828
  • 8
  • 54
  • 65
7
votes
2 answers

Finding the SQL output of a parameterized query

I'm making a parameterized query using C# against a SQL server 2005 instance, and I'd like to take a look at the SQL that is run against the database for debugging purposes. Is there somewhere I can look to see what the output SQL of the…
Dan Monego
  • 9,637
  • 6
  • 37
  • 72
6
votes
1 answer

Is it possible to pass a DataTable to an ad-hoc sql query in Entity Framework?

I'd like to be able to build a parameterized ad-hoc SQL query using Entity Framework which consumes a table-valued parameter. NB: The use-case which brought this to my interest was querying for multiple entities given a list of IDs. I want the query…
Oly
  • 2,329
  • 1
  • 18
  • 23
1
2 3
…
20 21