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.

Filter by
Sorted by
Tagged with
606votes
22answers
231kviews

Can I bind an array to an IN() condition?

I'm curious to know if it's possible to bind an array of values to a placeholder using PDO. The use case here is attempting to pass an array of values for use with an IN() condition. I'd like to ...
user avatar
  • 6,769
370votes
31answers
353kviews

PreparedStatement IN clause alternatives?

What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? ...
user avatar
212votes
9answers
158kviews

How can prepared statements protect from SQL injection attacks?

How do prepared statements help us prevent SQL injection attacks? Wikipedia says: Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using ...
user avatar
  • 11.3k
198votes
6answers
198kviews

Using "like" wildcard in prepared statement

I am using prepared statements to execute mysql database queries. And I want to implement a search functionality based on a keyword of sorts. For that I need to use LIKE keyword, that much I know. ...
user avatar
  • 2,461
183votes
13answers
288kviews

How can I get the SQL of a PreparedStatement?

I have a general Java method with the following method signature: private static ResultSet runSQLResultSet(String sql, Object... queryParams) It opens a connection, builds a PreparedStatement using ...
user avatar
  • 75.1k
181votes
8answers
363kviews

Get query from java.sql.PreparedStatement [duplicate]

In my code I am using java.sql.PreparedStatement. I then execute the setString() method to populate the wildcards of the prepared statement. Is there a way for me to retrieve (and print out) the ...
user avatar
  • 5,279
165votes
21answers
188kviews

PDO Prepared Inserts multiple rows in single query

I am currently using this type of SQL on MySQL to insert multiple rows of values in one single query: INSERT INTO `tbl` (`key1`,`key2`) VALUES ('r1v1','r1v2'),('r2v1','r2v2'),... On the readings on ...
user avatar
  • 2,996
143votes
10answers
124kviews

How does a PreparedStatement avoid or prevent SQL injection?

I know that PreparedStatements avoid/prevent SQL Injection. How does it do that? Will the final form query that is constructed using PreparedStatements be a string or otherwise?
user avatar
  • 13.1k
124votes
14answers
327kviews

PreparedStatement with list of parameters in a IN clause [duplicate]

How to set value for in clause in a preparedStatement in JDBC while executing a query. Example: connection.prepareStatement("Select * from test where field in (?)"); If this in-clause can hold ...
user avatar
  • 3,093
114votes
11answers
13kviews

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 ...
user avatar
  • 6,755
106votes
5answers
98kviews

How do I use prepared statements in SQlite in Android?

How do I use prepared statements in SQlite in Android?
user avatar
  • 265k
106votes
2answers
107kviews

Reusing a PreparedStatement multiple times

in the case of using PreparedStatement with a single common connection without any pool, can I recreate an instance for every dml/sql operation mantaining the power of prepared statements? I mean: ...
user avatar
  • 2,230
99votes
5answers
164kviews

PreparedStatement setNull(..)

Java PreparedStatement provides a possibility to explicitely set a Null value. This possibility is: prepStmt.setNull(parameterIndex, Types.VARCHAR); Are the semantics of this call the same as when ...
user avatar
  • 17.8k
95votes
9answers
82kviews

PHP - Using PDO with IN clause array

I'm using PDO to execute a statement with an IN clause that uses an array for its values: $in_array = array(1, 2, 3); $in_values = implode(',', $in_array); $my_result = $wbdb->prepare("SELECT *...
user avatar
  • 1,765
92votes
7answers
171kviews

Java: Insert multiple rows into MySQL with PreparedStatement

I want to insert multiple rows into a MySQL table at once using Java. The number of rows is dynamic. In the past I was doing... for (String element : array) { myStatement.setString(1, element[0]);...
user avatar
88votes
4answers
106kviews

Example of how to use bind_result vs get_result

I would like to see an example of how to call using bind_result vs. get_result and what would be the purpose of using one over the other. Also the pro and cons of using each. What is the limitation ...
user avatar
77votes
3answers
130kviews

MySQLi prepared statements error reporting [duplicate]

I'm trying to get my head around MySQli and I'm confused by the error reporting. I am using the return value of the MySQLi 'prepare' statement to detect errors when executing SQL, like this: $...
user avatar
  • 2,736
69votes
3answers
141kviews

Where's my invalid character (ORA-00911)

I'm trying to insert CLOBs into a database (see related question). I can't quite figure out what's wrong. I have a list of about 85 clobs I want to insert into a table. Even when inserting only the ...
user avatar
  • 23.7k
64votes
6answers
51kviews

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 = ...
user avatar
  • 8,288
63votes
6answers
257kviews

Using setDate in PreparedStatement

In order to make our code more standard, we were asked to change all the places where we hardcoded our SQL variables to prepared statements and bind the variables instead. I am however facing a ...
user avatar
  • 7,098
63votes
8answers
80kviews

How can I Insert JSON object into Postgres using Java preparedStatement?

I’m struggling to insert a JSON object into my postgres v9.4 DB. I have defined the column called "evtjson" as type json (not jsonb). I am trying to use a prepared statement in Java (jdk1.8) to ...
user avatar
63votes
5answers
96kviews

Is there a way to retrieve the autoincrement ID from a prepared statement

Is there a way to retrieve the auto generated key from a DB query when using a java query with prepared statements. For example, I know AutoGeneratedKeys can work as follows. stmt = conn....
user avatar
  • 8,980
62votes
6answers
124kviews

What does a question mark represent in SQL queries? [duplicate]

While going through some SQL books I found that examples tend to use question marks (?) in their queries. What does it represent?
user avatar
57votes
10answers
35kviews

PreparedStatements and performance

So I keep hearing that PreparedStatements are good for performance. We have a Java application in which we use the regular 'Statement' more than we use the 'PreparedStatement'. While trying to move ...
user avatar
  • 18.8k
54votes
4answers
81kviews

How to use a tablename variable for a java prepared statement insert [duplicate]

I am using a java PreparedStatment object to construct a series of batched INSERT queries. The query statement is of the format... String strQuery = "INSERT INTO ? (col1, col2, col3, col4, col5) ...
user avatar
53votes
3answers
46kviews

Using Prepared Statement, how I return the id of the inserted row?

I want retrieve the id of a inserted row in the database, but I don't know how to do this. I tried to return using the SQL clause RETURNING id, but not works. How I can return the id after the ...
user avatar
52votes
4answers
124kviews

What is parameterized query?

What is a parameterized query, and what would an example of such a query be in PHP and MySQL?
user avatar
51votes
3answers
35kviews

DIfference Between Stored Procedures and Prepared Statements?

What is the difference between Stored Procedures and Prepared Statements... And which one is better and why...!! I was trying to google it but haven't got any better article...
user avatar
  • 21.9k
51votes
7answers
52kviews

Does Python support MySQL prepared statements?

I worked on a PHP project earlier where prepared statements made the SELECT queries 20% faster. I'm wondering if it works on Python? I can't seem to find anything that specifically says it does or ...
user avatar
  • 8,926
50votes
7answers
55kviews

Variable column names using prepared statements

I was wondering if there was any way to specify returned column names using prepared statements. I am using MySQL and Java. When I try it: String columnNames="d,e,f"; //Actually from the ...
user avatar
  • 5,924
50votes
5answers
30kviews

How to deal with (maybe) null values in a PreparedStatement?

The statement is SELECT * FROM tableA WHERE x = ? and the parameter is inserted via java.sql.PreparedStatement 'stmt' stmt.setString(1, y); // y may be null If y is null, the statement returns no ...
user avatar
  • 10.1k
41votes
3answers
67kviews

Get last insert id after a prepared insert with PDO

I'm using PHP PDO with PostgreSQL for a new project. Given the following function, how can I return the id of the row just inserted? It doesn't work the way it looks now. function ...
user avatar
  • 7,863
39votes
4answers
124kviews

How to use an arraylist as a prepared statement parameter [duplicate]

I have looked and have been unable to find an answer to the following challenge I am having. It seems pretty straightforward but I have been unable to resolve it. I have an ArrayList of record ids ...
user avatar
39votes
1answer
51kviews

How to insert into MySQL using a prepared statement with PHP [duplicate]

I am just learning about databases and I want to be able to store user inputs. What would be a basic example on how to get form data and save it to a database using PHP? Also making the form secure ...
user avatar
  • 2,433
39votes
2answers
17kviews

SQLite/C# Connection Pooling and Prepared Statement Confusion

I have been spending some time reading different best practices for databases and for SQLite specifically. While reading I found I was doing many things I shouldn't be doing and when attempting to fix ...
user avatar
  • 451
38votes
3answers
53kviews

Java PreparedStatement retrieving last inserted ID [duplicate]

This answer to this question done this way seems to be very difficult to find on the internet. Basically I am inserting values into a MySQL database using PreparedStatement. I use the ...
user avatar
38votes
5answers
153kviews

Using prepared statements with JDBCTemplate

I'm using the JDBC template and want to read from a database using prepared statements. I iterate over many lines in a .csv file, and on every line I execute some SQL select queries with corresponding ...
user avatar
38votes
4answers
34kviews

Does the preparedStatement avoid SQL injection? [duplicate]

I have read and tried to inject vulnerable sql queries to my application. It is not safe enough. I am simply using the Statement Connection for database validations and other insertion operations. Is ...
user avatar
38votes
6answers
11kviews

In JDBC, why do parameter indexes for prepared statements begin at 1 instead of 0?

Everywhere else in Java, anything with an index starts at 0. Is there a reason for the change here or is this just bad design?
user avatar
  • 58.7k
35votes
7answers
86kviews

Return number of rows affected by SQL UPDATE statement in Java

I'm using a MySQL database and accessing it through Java. PreparedStatement prep1 = this.connection.prepareStatement( "UPDATE user_table SET Level = 'Super' WHERE Username ...
user avatar
  • 8,935
35votes
4answers
78kviews

Bulk insert in Java using prepared statements batch update

I am trying to fill a resultSet in Java with about 50,000 rows of 10 columns and then inserting them into another table using the batchExecute method of PreparedStatement. To make the process faster ...
user avatar
  • 1,481
35votes
1answer
39kviews

Php mysqi bind_param Number of variables doesn't match number of parameters in prepared statement [duplicate]

This has to be a newbie mistake, but I'm not seeing it. Here is a snippet from my code: $mysqli = mysqli_connect($dbCredentials['hostname'], $dbCredentials['username'], $dbCredentials['password']...
user avatar
  • 625
34votes
6answers
12kviews

When *not* to use prepared statements?

I'm re-engineering a PHP-driven web site which uses a minimal database. The original version used "pseudo-prepared-statements" (PHP functions which did quoting and parameter replacement) to prevent ...
user avatar
  • 52.1k
33votes
5answers
58kviews

PDO were rows affected during execute statement

I have found many ways to use the exec statement for PDO, but I'm not sure it helps me. My understanding is that I have to use the execute() function for prepared statements. I am updating a row ...
user avatar
  • 1,926
33votes
1answer
73kviews

How to prepare statement for update query? [duplicate]

I have a mysqli query with the following code: $db_usag->query("UPDATE Applicant SET phone_number ='$phone_number', street_name='$street_name', city='$city', county='$county', zip_code='$...
user avatar
  • 6,269
33votes
12answers
34kviews

SQLite: bind list of values to "WHERE col IN ( :PRM )"

all I want to do is send a query like SELECT * FROM table WHERE col IN (110, 130, 90); So I prepared the following statement SELECT * FROM table WHERE col IN (:LST); Then I use sqlite_bind_text(...
user avatar
  • 1,539
33votes
2answers
18kviews

pdo prepared statements with wildcards

I want to execute the following mysql query: SELECT * FROM `gc_users` WHERE `name` LIKE '%anyname%' I tried this without success: $stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` ...
user avatar
  • 3,571
32votes
8answers
105kviews

How to set list of parameters on prepared statement? [duplicate]

i have a list of names e.g.: List<String> names = ... names.add('charles'); ... and a statement: PreparedStatement stmt = conn.prepareStatement('select * from person where name in ( ? )'); ...
user avatar
  • 14.8k
31votes
3answers
65kviews

JDBC - How to set char in a prepared statement

I cannot find any method like char c = 'c'; preparedStatement.setChar(1, c); How to set character to a prepared statement?
user avatar
  • 32.3k
31votes
4answers
25kviews

PHP PDO::bindParam() data types.. how does it work?

I'm wondering what the declaration of the data type in bindParam() (or bindValue()) is used for... I mean, I thought that if I define an integer argument (PDO::PARAM_INT), the argument must be ...
user avatar
  • 17.6k

15 30 50 per page
1
2 3 4 5
130