prepared-statement – Page 5

Java: Insert multiple rows into MySQL with PreparedStatement

January 19, 2023 by Tarik

You can create a batch by PreparedStatement#addBatch() and execute it by PreparedStatement#executeBatch(). Here’s a kickoff example: public void save(List<Entity> entities) throws SQLException { try ( Connection connection = database.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL_INSERT); ) { int i = 0; for (Entity entity : entities) { statement.setString(1, entity.getSomeProperty()); // … statement.addBatch(); i++; if (i % 1000 … Read more

PreparedStatement setNull(..)

January 8, 2023 by Tarik

but watch out for this…. Long nullLong = null; preparedStatement.setLong( nullLong ); -thows null pointer exception- because the protype is setLong( long ) NOT setLong( Long ) nice one to catch you out eh.

How do I create a parameterized SQL query? Why Should I?

January 7, 2023 by Tarik

The EXEC example in the question would NOT be parameterized. You need parameterized queries (prepared statements in some circles) to prevent input like this from causing damage: ‘;DROP TABLE bar;– Try putting that in your fuz variable (or don’t, if you value the bar table). More subtle and damaging queries are possible as well. Here’s … Read more

Reusing a PreparedStatement multiple times

January 1, 2023 by Tarik

The second way is a tad more efficient, but a much better way is to execute them in batches: public void executeBatch(List<Entity> entities) throws SQLException { try ( Connection connection = dataSource.getConnection(); PreparedStatement statement = connection.prepareStatement(SQL); ) { for (Entity entity : entities) { statement.setObject(1, entity.getSomeProperty()); // … statement.addBatch(); } statement.executeBatch(); } } You’re however … Read more

PHP – Using PDO with IN clause array

December 31, 2022 by Tarik

PDO is not good with such things. You need to create a string with placeholders dynamically and insert it into the query, while binding array values the usual way. With positional placeholders it would be like this: $in = str_repeat(‘?,’, count($in_array) – 1) . ‘?’; $sql = “SELECT * FROM my_table WHERE my_value IN ($in)”; … Read more

How do I use prepared statements in SQlite in Android?

December 30, 2022 by Tarik

For prepared SQLite statements in Android there is SQLiteStatement. Prepared statements help you speed up performance (especially for statements that need to be executed multiple times) and also help avoid against injection attacks. See this article for a general discussion on prepared statements. SQLiteStatement is meant to be used with SQL statements that do not … Read more

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

December 11, 2022 by Tarik

What I do is to add a “?” for each possible value. var stmt = String.format(“select * from test where field in (%s)”, values.stream() .map(v -> “?”) .collect(Collectors.joining(“, “))); Alternative using StringBuilder (which was the original answer 10+ years ago) List values = … StringBuilder builder = new StringBuilder(); for( int i = 0 ; … Read more

How does a PreparedStatement avoid or prevent SQL injection?

November 20, 2022 by Tarik

Consider two ways of doing the same thing: PreparedStatement stmt = conn.createStatement(“INSERT INTO students VALUES(‘” + user + “‘)”); stmt.execute(); Or PreparedStatement stmt = conn.prepareStatement(“INSERT INTO student VALUES(?)”); stmt.setString(1, user); stmt.execute(); If “user” came from user input and the user input was Robert’); DROP TABLE students; — Then in the first instance, you’d be hosed. … Read more

PDO Prepared Inserts multiple rows in single query

November 12, 2022 by Tarik

Multiple Values Insert with PDO Prepared Statements Inserting multiple values in one execute statement. Why because according to this page it is faster than regular inserts. $datafields = array(‘fielda’, ‘fieldb’, … ); $data[] = array(‘fielda’ => ‘value’, ‘fieldb’ => ‘value’ ….); $data[] = array(‘fielda’ => ‘value’, ‘fieldb’ => ‘value’ ….); more data values or you … Read more

Get query from java.sql.PreparedStatement [duplicate]

October 21, 2022 by Tarik

This is nowhere definied in the JDBC API contract, but if you’re lucky, the JDBC driver in question may return the complete SQL by just calling PreparedStatement#toString(). I.e. System.out.println(preparedStatement); To my experience, the ones which currently do so are at least the PostgreSQL 8.x and MySQL 5.x JDBC drivers. In the case that your JDBC … Read more