Stack Overflow
Stack Overflow

Questions tagged [prepared-statement]

A Prepared Statement (or parameterized statement) is a precompiled SQL statement that serves to improve performance and mitigate SQL injection attacks. Prepared statements are used in many popular Relational Database Management Systems.

Prepared statements separate data binding from execution. Separating statement preparation from execution can be more efficient for statements that are executed multiple times, because the preparation phase need be done only once. For example, if you need to insert a bunch of rows, you can prepare an INSERT statement once and then execute it repeatedly, binding successive row values to it for each execution. A prepared statement can contain placeholders to indicate where data values should appear. After you prepare the statement, bind specific values to the placeholders (either before or at statement-execution time), then substitute the values into the statement before sending it to the database server.

Also see: ,

6193 questions
29
votes
2 answers

Which should I close first, the PreparedStatement or the Connection?

When using a PreparedStatement in JDBC, should I close the PreparedStatement first or the Connection first? I just saw a code sample in which the Connection is closed first, but it seems to me more logical to close the PreparedStatement first. Is…
froadie
  • 79,995
  • 75
  • 166
  • 235
29
votes
3 answers

PHP UPDATE prepared statement

I'm trying to learn the proper way to use prepared statements to avoid SQL injections etc. When I execute the script I get a message from my script saying 0 Rows Inserted, I expect this to say 1 Rows Inserted and of course update the table. I'm not…
user0129e021939232
  • 6,205
  • 24
  • 87
  • 140
28
votes
1 answer

Binding params for PDO statement inside a loop

I'm trying to bind parametres for SQL query inside a loop: $db = new PDO('mysql:dbname=test;host=localhost', 'test', ''); $stmt = $db->prepare('INSERT INTO entries VALUES (NULL, ?, ?, ?, NULL)'); $title = 'some titile'; $post = 'some…
Buddy
  • 1,808
  • 3
  • 19
  • 28
28
votes
6 answers

PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table

I'm using a bit(1) field to store boolean values and writing into the table using PDO prepared statements. This is the test table: CREATE TABLE IF NOT EXISTS `test` ( `SomeText` varchar(255) NOT NULL, `TestBool` bit(1) NOT NULL DEFAULT b'0' )…
Peter
  • 361
  • 1
  • 3
  • 6
27
votes
1 answer

What's the difference between cachePrepStmts and useServerPrepStmts in MySQL JDBC Driver

The MySQL JDBC Driver defines these two properties as: useServerPrepStmts - Use server-side prepared statements if the server supports them? cachePrepStmts - Should the driver cache the parsing stage of PreparedStatements of client-side prepared…
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
27
votes
7 answers

WordPress prepared statement with IN() condition

I have three values in a string like this: $villes = '"paris","fes","rabat"'; When I feed it into a prepared statement like this: $sql = 'SELECT distinct telecopie FROM `comptage_fax` WHERE `ville` IN(%s)'; $query = $wpdb->prepare($sql,…
mgraph
  • 15,238
  • 4
  • 41
  • 75
26
votes
4 answers

Can PreparedStatement.addBatch() be used for SELECT queries?

Imagine that I have 100 SELECT queries that differ by one input. A PreparedStatement can be used for the value. All the documentation I see on the Web is for batch insert/update/delete. I have never seen batches used for select statements. Can…
kevinarpe
  • 20,319
  • 26
  • 127
  • 154
26
votes
3 answers

using JDBC preparedStatement in a batch

Im using Statements batchs to query my data base. Iv'e done some research now and i want to rewrite my application to use preparedStatement instead but i'm having hard time to figure out how to add queries to a preparedStatement batch. This is what…
Boris C
  • 669
  • 2
  • 7
  • 14
26
votes
2 answers

When to close Prepared Statement

When to close prepared statements in PHP? Example: $query = "insert into web_reviews (title,added_date,reviewer_home_url,read_more_link,summary) values(?,?,?,?,?)"; $stmt = $this->db->prepare($query); …
Tarik
  • 79,711
  • 83
  • 236
  • 349
26
votes
4 answers

'PDOException' with message 'SQLSTATE[22001]: String data, right truncated: 0

NOTE: I have narrowed this problem down to specifically PDO because I am able to successfully prepare and execute statements using the odbc_* functions. Why can't I bind this parameter to the PDO prepared statement? This works: $mssqldriver = 'ODBC…
Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
26
votes
7 answers

MySQL Prepared statements with a variable size variable list

How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time? An example such query is: SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33) The IN clause will have a different number of ids…
smarthall
  • 288
  • 1
  • 3
  • 7
26
votes
4 answers

Use of PDO in classes

I have a few classes that perform some MySQL queries and prepared statements. However, I am lost in how to incorporate my PDO object within those classes. For example, I want to do something like this:
axsuul
  • 7,370
  • 9
  • 54
  • 71
26
votes
2 answers

$stmt->close() vs $stmt->free_result()

I am trying to clarify the difference between $stmt->close() and $stmt->free_result() when finalizing a prepared mysqli statement. So far I use: $mysqli = new mysqli(host,user,password,database); $stmt = $mysqli->prepare(sql…
Nicholaos Renessis
  • 432
  • 1
  • 4
  • 8
25
votes
2 answers

Getting java.sql.SQLException: Operation not allowed after ResultSet closed

When I execute the following code, I get an exception. I think it is because I'm preparing in new statement with he same connection object. How should I rewrite this so that I can create a prepared statement AND get to use rs2? Do I have to create a…
samxli
  • 1,536
  • 5
  • 17
  • 28
25
votes
1 answer

What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

In PostgreSQL, what is the difference between a prepared statement and a SQL or PL/pgSQL function, in terms of their purposes, advantages and disadvantages? When shall we use which? In this very simple example, do they work the same, correct?…
user3284469
1 2 3
…
99 100