Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [prepared-statement]

78 questions
44
votes
1 answer

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
MickeyfAgain_BeforeExitOfSO
  • 691
  • 1
  • 6
  • 12
19
votes
3 answers

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable to the LIMIT clause. I have this stored procedure…
donk
  • 525
  • 2
  • 6
  • 13
16
votes
2 answers

What is the sense and benefit of using SqlCommand.Prepare()?

I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of…
Magier
  • 4,697
  • 8
  • 40
  • 83
12
votes
3 answers

How to use insert delay with the InnoDB engine and use less connection for insert statements?

I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. Through insert statements multiple clients insert…
Shashank
  • 397
  • 4
  • 5
  • 15
11
votes
1 answer

PostgreSQL PREPARE query with IN () parameters

I'm trying to prepare a query from PHP like: pg_prepare($con, "prep", "select * from test where tid in ($1)"); and then execute it with: $strpar = "3,4,6,8,10"; pg_execute($con, "prep", array($strpars)); The problem is that I cannot pass a series…
Fabrizio Mazzoni
  • 1,910
  • 4
  • 22
  • 31
8
votes
1 answer

sp_prepexec (sp_execute) vs. sp_executeSQL

The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them? I am not a DBA, so please use little words.…
J.T.
  • 203
  • 2
  • 5
8
votes
1 answer

List Prepared Statements

Anybody know how I can list the prepared statements that are in a SQL Server instance?
dublintech
  • 1,479
  • 8
  • 21
  • 26
8
votes
1 answer

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`; DELIMITER ;; CREATE DEFINER =…
Abdul Manaf
  • 9,419
  • 16
  • 69
  • 83
7
votes
2 answers

Can I execute a prepared statement inside a MySQL SELECT?

I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all authorized/unlocked sellers from a 2nd table (any number of…
frequent
  • 263
  • 2
  • 3
  • 10
7
votes
2 answers

Microsoft SQL Server: Prepared Statements

How do you create and use a prepared statement in SQL Server? In PostgreSQL, you do something like: PREPARE prepared (varchar, varchar, etc) AS INSERT INTO table(field,field,etc) VALUES ($1,$2,etc); EXECUTE (value,value,etc); Something…
Manngo
  • 2,487
  • 6
  • 27
  • 49
6
votes
1 answer

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter binding for the values that are to be part of the…
Erin Schoonover
  • 163
  • 1
  • 6
5
votes
1 answer

Tracking errors from prepared SQL statements

I use extended events to store database errors, like this: CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.client_hostname, sqlserver.database_id, …
carlo.borreo
  • 1,447
  • 5
  • 19
  • 34
4
votes
2 answers

DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

I have data in a java object as data1, data2. data1 and data2 together forms a composite key in myTable where I want to insert the object. The writing is happening as a batch. Like 10 insert statements are prepared using 10 objects and are executed…
Nikunj Chauhan
  • 41
  • 1
  • 2
4
votes
1 answer

Should a partial index with WHERE NOT NULL be used if queries use a JDBC prepared statement?

Suppose we create a partial index in Postgres that avoids useless NULL data: CREATE INDEX my_ix ON my (col1) WHERE col1 IS NOT NULL; Should I write the SELECT query as: SELECT * FROM my WHERE col1 = 'abc'; or as: SELECT * FROM my WHERE col1 =…
gavenkoa
  • 457
  • 2
  • 8
  • 17
3
votes
1 answer

Prepared statement overhead

I am using libpqxx to communicate with a PostgreSQL 9.3 server. For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each. If they are all wrapped up into a single CTE, the entire prepared…
Jim Bob
  • 555
  • 4
  • 18
1
2 3 4 5 6