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
4
votes
2 answers

Execute prepared statement in a batch in NodeJS using node-mssql?

In java I can send multiple set of parameters of a parameterized query in one go (see code example below). How can I accomplish the same result in NodeJs, using node-mssql package? PreparedStatement ps = c.prepareStatement("INSERT INTO employees…
Liquid Studios Seattle
  • 57
  • 1
  • 7
4
votes
1 answer

Preventing SQL Injection in C

I am writing a C application that takes some user input and does a few database queries. I am well aware of the risks here of SQL injection and wish to prevent it. Ideally I would use parameterized queries, but have been unable to find anything with…
DanielGibbs
  • 9,910
  • 11
  • 76
  • 121
4
votes
3 answers

Error with simple Parameterized Query - Java/ SQL

Following on from one of my previous questions to do with method design I was advised to implemented my SQL queries as a parameterized query as opposed to a simple string. I've never used parameterized queries before so I decided to start with…
Jamie Keeling
  • 9,806
  • 17
  • 65
  • 102
4
votes
1 answer

data reader is incompatible with the specified. does not have a corresponding column in the data reader with the same name

I keep getting the below exception. The exception baffles me because I am not selecting UserID. I have tried changing the select to SELECT * but this just causes a result.Count of 0 regardless if the data exists or not. I have a dummy record in the…
Adam Schneider
  • 275
  • 1
  • 5
  • 18
4
votes
1 answer

How to use parameterized SQL with dplyr?

I'm trying to execute a SQL query with dplyr on SQL Server: tbl(con, sql(sqlQuery)) The query is generated dynamically using sprintf("SELECT ... WHERE a = '%s'). This is a bad practice because it can be abused for SQL injection, but I can't find…
ckarras
  • 4,946
  • 2
  • 33
  • 37
4
votes
1 answer

Query formatting for Parameterized Queries

I am using pg-promise to execute select query with like clause in PostgreSQL. Unfortunately the query is failing with error code as 08P01 and error message bind message supplies 1 parameters, but prepared statement "" requires 0 The query is as…
CuriousMind
  • 3,143
  • 3
  • 29
  • 54
4
votes
3 answers

preg_replace() is replacing too much

I am working on a simple SQL debugger which will accept parameterized variables and try to replace them accordingly so that if a piece of SQL has an issue then I can copy+paste it directly into my RDBMS to work with the query and hopefully debug an…
MonkeyZeus
  • 20,375
  • 4
  • 36
  • 77
4
votes
2 answers

Why SQL Server SHOWPLAN_XML via DbCommand(Parameterized) is not return results?

I wish to collect Query plan via C# for improve development. My approach is use DbCommand and SET SHOWPLAN_XML ON But... Non-parameterized query will return query plan collectly. Parameterized query will return nothing! Both SQL Server 2008 R2 and…
Lyc
  • 121
  • 8
4
votes
1 answer

JPA query for select which multiple values in the "IN" clause

I want to create a JPA parameterised query for following SQL statement select * from car where (colour, speed) in (('red', 50), ('blue', 70)) this query returns the expected result entityManager.createQuery("from Car c where (c.colour, c.speed) in…
SubOptimal
  • 22,518
  • 3
  • 53
  • 69
4
votes
4 answers

SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size

I tried to insert some values in SQL database by the code: var connstring = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString; ; var conn = new SqlConnection(connstring); conn.Open(); SqlCommand command = new…
Aan
  • 12,247
  • 36
  • 89
  • 150
4
votes
3 answers

Parameterized query binding in ON clause for a LEFT JOIN in Laravel Eloquent / Query Builder

Let's say I want to show a full list of awards with type="color": Awards Type 2013 Winner ====== ==== =========== Blue Award color Tom Red Award color Green Award color Dan To achieve this result I could…
prograhammer
  • 20,132
  • 13
  • 91
  • 118
4
votes
1 answer

Strange behaviour of parameterized SQLite query

(Feel free to improve the question title if you can think of something better.) Question: Consider the following SQLite query: SELECT COUNT(*) FROM (SELECT 1 AS value UNION SELECT 2 AS value) WHERE value <= ? When I use 1 as the parameter, I…
Heinzi
  • 167,459
  • 57
  • 363
  • 519
3
votes
3 answers

Is this a correct way to allow wildcard search for a user?

Given a textbox name for example, the user requirement wants to be able to do a wildcard search (such as contains, starts with, ends with). Is it ok to accept the sql wildcard characters ('%' and '_') as input as long as I am still using…
Carlos Jaime C. De Leon
  • 2,476
  • 2
  • 37
  • 53
3
votes
1 answer

Postgres + Node - Parameter Query with $1, $2, $3 giving error

I'm getting this nodejs error when I try running my query with params ($1, $2, $3). The query works in postgres. It also works if I don't use the params ($1, $2, $3) and instead substitute values in for them. Could someone help me identify the…
Alice W.
  • 81
  • 5
3
votes
0 answers

Why Number Value in Hibernate JPA CriteriaAPI Where Clause is not Parameterized?

I have noticed following, when running JPA-CriteriaAPI with Hibernate. Database table CREATE TABLE kernel_group ( groupId NUMBER(10,0) NOT NULL, groupName VARCHAR(64) NOT NULL UNIQUE, autoGroup NUMBER(1,0) DEFAULT (0), jpaVersion…
Tarana
  • 119
  • 1
  • 9
1 2
3
…
20 21