I want retrieve the id of a inserted row in the database, but I don&#39;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 insertion of a row?

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

I am using a java PreparedStatment object to construct a series of batched INSERT queries.  The query statement is of the format...

    String strQuery = &quot;INSERT INTO ? (col1, col2, col3, col4, col5) VALUES (?,?,?,?,?,?);&quot;;

...so both field values and the tablename are variables (ie. I have multiple tables with the same column format of which each insert will be directed to a different one).  I can get the executes to work if I remove the &quot;?&quot; tablename variable and hard code but each prepared statement will be inserted into a different table so needs to remain a variable I populate immediately prior to executing the batch query using...

    stmt.setString(1, &quot;tableName1&quot;);

How can I let this be a dynamic variable please?


How to use a tablename variable for a java prepared statement insert

I worked on a PHP project earlier where prepared statements made the SELECT queries 20% faster.

I&#39;m wondering if it works on Python? I can&#39;t seem to find anything that specifically says it does or does NOT.



Does Python support MySQL 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=&quot;d,e,f&quot;; //Actually from the user...
    String name = &quot;some_table&quot;; //From user...
    String query = &quot;SELECT a,b,c,? FROM &quot; + name + &quot; WHERE d=?&quot;;//...
    stmt = conn.prepareStatement(query);
    stmt.setString(1, columnNames);
    stmt.setString(2, &quot;x&quot;);


I get this type of statement (printing right before execution).

    SELECT a,b,c,&#39;d,e,f&#39; FROM some_table WHERE d=&#39;x&#39;

I would, however, like to see:

    SELECT a,b,c,d,e,f FROM some_table WHERE d=&#39;x&#39;

I know that I cannot do this for table names, as discussed
[here](https://stackoverflow.com/questions/1208442/using-prepared-statements-to-set-table-name), but was wondering if there was some way to do it for column names.

If there is not, then I will just have to try and make sure that I sanitize the input so it doesn&#39;t lead to SQL injection vulnerabilities.

Variable column names using 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&#39;t got any better article...

DIfference Between Stored Procedures and Prepared Statements?

The statement is

    SELECT * FROM tableA WHERE x = ?

and the parameter is inserted via java.sql.PreparedStatement &#39;stmt&#39;

    stmt.setString(1, y); // y may be null

If `y` is null, the statement returns no rows in every case because `x = null` is always false (should be `x IS NULL`).
One solution would be

    SELECT * FROM tableA WHERE x = ? OR (x IS NULL AND ? IS NULL)

But then i have to set the same parameter twice. Is there a better solution?

Thanks!

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

How do [prepared statements][1] help us prevent [SQL injection][2] attacks?

Wikipedia says:

&gt; Prepared statements are resilient against SQL injection, because
&gt; parameter values, which are transmitted later using a different
&gt; protocol, need not be correctly escaped. If the original statement
&gt; template is not derived from external input, SQL injection cannot
&gt; occur.

I cannot see the reason very well. What would be a simple explanation in an easy English and some examples?

  [1]: http://en.wikipedia.org/wiki/Prepared_statement
  [2]: http://en.wikipedia.org/wiki/SQL_injection



How can prepared statements protect from SQL injection attacks?

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. And I have also used prepared statements before, but I do not know how to use it with `LIKE` because from the following code where would I add the `&#39;keyword%&#39;`? 

Can I directly use it in the `pstmt.setString(1, notes)` as `(1, notes+&quot;%&quot;)` or something like that. I see a lot of posts on this on the web but no good answer anywhere.

    PreparedStatement pstmt = con.prepareStatement(
          &quot;SELECT * FROM analysis WHERE notes like ?&quot;);
    pstmt.setString(1, notes);
    ResultSet rs = pstmt.executeQuery();

Using &quot;like&quot; wildcard in prepared statement

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 the sql statement and the parameters in the `queryParams` variable length array, runs it, caches the `ResultSet` (in a `CachedRowSetImpl`), closes the connection, and returns the cached result set.

I have exception handling in the method that logs errors. I log the sql statement as part of the log since it&#39;s very helpful for debugging. My problem is that logging the String variable `sql` logs the template statement with ?&#39;s instead of actual values. I want to log the *actual* statement that was executed (or tried to execute).

So... Is there any way to get the actual SQL statement that will be run by a `PreparedStatement`? (*Without* building it myself. If I can&#39;t find a way to access the `PreparedStatement&#39;s` SQL, I&#39;ll probably end up building it myself in my `catch`es.)



How can I get the SQL of a PreparedStatement?

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 final query before the `executeQuery()` method is called and the query is executed?  I Just want this for debugging purposes.

Get query from java.sql.PreparedStatement

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 (&#39;r1v1&#39;,&#39;r1v2&#39;),(&#39;r2v1&#39;,&#39;r2v2&#39;),...

On the readings on PDO, the use prepared statements should give me a better security than static queries.

I would therefore like to know whether it is possible to generate &quot;inserting multiple rows of values by the use of one query&quot; using prepared statements. 


If yes, may I know how can I implement it?

PDO Prepared Inserts multiple rows in single query

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?

How does a PreparedStatement avoid or prevent SQL injection?

I&#39;m curious to know if it&#39;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&#39;d like to be able to do something like this:

    &lt;?php
    $ids=array(1,2,3,7,8,9);
    $db = new PDO(...);
    $stmt = $db-&gt;prepare(
    	&#39;SELECT *
    	 FROM table
    	 WHERE id IN(:an_array)&#39;
    );
    $stmt-&gt;bindParam(&#39;an_array&#39;,$ids);
    $stmt-&gt;execute();
    ?&gt;

And have PDO bind and quote all the values in the array.

At the moment I&#39;m doing:

    &lt;?php
    $ids = array(1,2,3,7,8,9);
    $db = new PDO(...);
    foreach($ids as &amp;$val)
    	$val=$db-&gt;quote($val); //iterate through array and quote
    $in = implode(&#39;,&#39;,$ids); //create comma separated list
    $stmt = $db-&gt;prepare(
    	&#39;SELECT *
    	 FROM table
    	 WHERE id IN(&#39;.$in.&#39;)&#39;
    );
    $stmt-&gt;execute();
    ?&gt;

Which certainly does the job, but just wondering if there&#39;s a built in solution I&#39;m missing?

Can I bind an array to an IN() condition in a PDO query?

How to set value for in clause in a preparedStatement in JDBC while executing a query.

Example:

    connection.prepareStatement(&quot;Select * from test where field in (?)&quot;);

If this in-clause can hold multiple values how can I do it. Sometimes I know the list of parameters beforehand or sometimes I don&#39;t know beforehand. How to handle this case?



PreparedStatement with list of parameters in a IN clause

How do I use prepared statements in SQlite in Android?

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:

    for (int i=0; i&lt;1000; i++) {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, someValue);
        preparedStatement.executeQuery();
        preparedStatement.close();
    }

instead of:

    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    for (int i=0; i&lt;1000; i++) {
        preparedStatement.clearParameters();
        preparedStatement.setObject(1, someValue);
        preparedStatement.executeQuery();
    }
    preparedStatement.close();

my question arises by the fact that I want to put this code into a multithreaded environment, can you give me some advice? thanks

Reusing a PreparedStatement multiple times

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 using a specific setType with a null parameter?

    prepStmt.setString(null);

?

PreparedStatement setNull(..)

I&#39;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(&#39;,&#39;, $in_array);
	$my_result = $wbdb-&gt;prepare(&quot;SELECT * FROM my_table WHERE my_value IN (&quot;.$in_values.&quot;)&quot;);
	$my_result-&gt;execute();
	$my_results = $my_result-&gt;fetchAll();

&lt;br&gt;
The above code works perfectly fine, but my question is why this doesn&#39;t:

     $in_array = array(1, 2, 3);
        $in_values = implode(&#39;,&#39;, $in_array);
    	$my_result = $wbdb-&gt;prepare(&quot;SELECT * FROM my_table WHERE my_value IN (:in_values)&quot;);
    	$my_result-&gt;execute(array(&#39;:in_values&#39; =&gt; $in_values));
    	$my_results = $my_result-&gt;fetchAll();

This code will return the item whose `my_value` equals the first item in the `$in_array` (1), but not the remaining items in the array (2, and 3).

PHP - Using PDO with IN clause array

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]);
        myStatement.setString(2, element[1]);
        
        myStatement.executeUpdate();
    }

I&#39;d like to optimize this to use the MySQL-supported syntax:

    INSERT INTO table (col1, col2) VALUES (&#39;val1&#39;, &#39;val2&#39;), (&#39;val1&#39;, &#39;val2&#39;)[, ...]

but with a `PreparedStatement` I don&#39;t know of any way to do this since I don&#39;t know beforehand how many elements `array` will contain. If it&#39;s not possible with a `PreparedStatement`, how else can I do it (and still escape the values in the array)?

Java: Insert multiple rows into MySQL with PreparedStatement

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 of using either and is there a difference.

Example of how to use bind_result vs get_result

I&#39;m trying to get my head around MySQli and I&#39;m confused by the error reporting.
I am using the return value of the MySQLi &#39;prepare&#39; statement to detect errors when executing SQL, like this:

    $stmt_test =  $mysqliDatabaseConnection-&gt;stmt_init();
    if($stmt_test-&gt;prepare(&quot;INSERT INTO testtable VALUES (23,44,56)&quot;))
    {
     $stmt_test-&gt;execute();
     $stmt_test-&gt;close();
    }
    else echo(&quot;Statement failed: &quot;. $stmt_test-&gt;error . &quot;&lt;br&gt;&quot;);

But, is the return value of the prepare statement only detecting if there is an error in the preperation of the SQL statement and not detecting execution errors? If so should I therefore change my execute line to flag errors as well like this:

    if($stmt_test-&gt;execute()) $errorflag=true;

And then just to be safe should I also do the following after the statement has executed:

    if($stmt_test-&gt;errno) {$errorflag=true;}

...Or was I OK to start with  and the return value on the MySQLi prepare&#39; statement captures all errors associated with the complete execution of the query it defines?

Thanks 
C

MySQLi prepared statements error reporting

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 `?` placeholder represents one value, rather than a list of values.

Consider the following SQL statement:

    SELECT my_column FROM my_table where search_column IN (?)

Using `preparedStatement.setString( 1, &quot;&#39;A&#39;, &#39;B&#39;, &#39;C&#39;&quot; );` is essentially a non-working attempt at a workaround of the reasons for using `?` in the first place.  

What workarounds are available?


PreparedStatement IN clause alternatives?

I&#39;m trying to insert `CLOB`s into a database (see [related question][1]). I can&#39;t quite figure out what&#39;s wrong. I have a list of about 85 clobs I want to insert into a table. Even when inserting only the first clob I get `ORA-00911: invalid character`. I can&#39;t figure out how to get the statement out of the `PreparedStatement` before it executes, so I can&#39;t be 100% certain that it&#39;s right, but if I got it right, then it should look exactly like this:

    insert all
      into domo_queries values (&#39;select 
    substr(to_char(max_data),1,4) as year,
    substr(to_char(max_data),5,6) as month,
    max_data
    from dss_fin_user.acq_dashboard_src_load_success
    where source = &#39;&#39;CHQ PeopleSoft FS&#39;&#39;&#39;)
    select * from dual;

Ultimately, this [`insert all`][2] statement would have a lot of `into`&#39;s, which is why I just don&#39;t do a regular `insert` statement. I don&#39;t see an invalid character in there, do you? (Oh, and that code above **runs fine when I run it in my sql developer tool**.) And I if I remove the semi-colon in the `PreparedStatement`, it throws an `ORA-00933: SQL command not properly ended` error.

In any case, here&#39;s my code for executing the query (and the values of the variables for the example above).

    public ResultSet executeQuery(String connection, String query, QueryParameter... params) throws DataException, SQLException {
      // query at this point = &quot;insert all
                              //into domo_queries values (?)
                              //select * from dual;&quot;
      Connection conn = ConnectionPool.getInstance().get(connection);
      PreparedStatement pstmt = conn.prepareStatement(query);
      for (int i = 1; i &lt;= params.length; i++) {
        QueryParameter param = params[i - 1];
        switch (param.getType()) { //The type in the example is QueryParameter.CLOB
          case QueryParameter.CLOB:
            Clob clob = CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
            clob.setString(i, &quot;&#39;&quot; + param.getValue() + &quot;&#39;&quot;);
            //the value of param.getValue() at this point is:
            /*
             * select 
             * substr(to_char(max_data),1,4) as year,
             * substr(to_char(max_data),5,6) as month,
             * max_data
             * from dss_fin_user.acq_dashboard_src_load_success
             * where source = &#39;&#39;CHQ PeopleSoft FS&#39;&#39;
             */
            pstmt.setClob(i, clob);
            break;
          case QueryParameter.STRING:
            pstmt.setString(i, &quot;&#39;&quot; + param.getValue() + &quot;&#39;&quot;);
            break;
        }
      }
      ResultSet rs = pstmt.executeQuery(); //Obviously, this is where the error is thrown
      conn.commit();
      ConnectionPool.getInstance().release(conn);
      return rs;
    }

Is there anything I&#39;m just missing big time?


  [1]: https://stackoverflow.com/questions/10727521/
  [2]: http://www.techonthenet.com/oracle/questions/insert_rows.php

Where&#39;s my invalid character (ORA-00911)

I’m struggling to insert a JSON object into my postgres v9.4 DB.  I have defined the column called &quot;evtjson&quot; as type `json`  (not `jsonb`).

I am trying to use a prepared statement in Java (jdk1.8) to insert a Json object (built using JEE javax.json libraries) into the column, but I keep running into SQLException errors. 

I create the JSON object using:


    JsonObject mbrLogRec = Json.createObjectBuilder().build();
    …
    mbrLogRec = Json.createObjectBuilder()
					.add(&quot;New MbrID&quot;, newId)
					.build();
Then I pass this object as a parameter to another method to write it to the DB using a prepared statement. (along with several other fields)   As:

    pStmt.setObject(11, dtlRec);

Using this method, I receive the following error:
&gt;org.postgresql.util.PSQLException: No hstore extension installed.
	at org.postgresql.jdbc.PgPreparedStatement.setMap(PgPreparedStatement.java:553)
	at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1036)

I have also tried:

    pStmt.setString(11, dtlRec.toString());
    pStmt.setObject(11, dtlRec.toString());

Which produce a different error:
&gt;Event JSON:  {&quot;New MbrID&quot;:29}

&gt;SQLException: ERROR: column &quot;evtjson&quot; is of type json but expression is of type character varying

&gt;Hint: You will need to rewrite or cast the expression.

But, at least this tells me that the DB is recognizing the column as type JSON.
I did try installing the hstore extension, but it then told me that it was not an hstore object.

OracleDocs shows a number of various methods to set the parameter value in the preparedStatement, but I&#39;d rather not try them all if someone knows the answer. (http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html) These also reference an additional parameter, SQLType, but I can&#39;t find any reference to these.

Should I try `setAsciiStream`? `CharacterStream`? CLOB?


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

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.createStatement();

    stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
    if(returnLastInsertId) {
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        auto_id = rs.getInt(1);
    } 

However. What if I want to do an insert with a prepared Statement. 

    String sql = &quot;INSERT INTO table (column1, column2) values(?, ?)&quot;;
    stmt = conn.prepareStatement(sql);
            
    //this is an error
    stmt.executeUpdate(Statement.RETURN_GENERATED_KEYS);
    if(returnLastInsertId) {
        //this is an error since the above is an error
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        auto_id = rs.getInt(1);
    } 

Is there a way to do this that I don&#39;t know about. It seems from the javadoc that PreparedStatements can&#39;t return the Auto Generated ID.

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

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 problem with the `setDate()`. 

Here is the code:

            DateFormat dateFormatYMD = new SimpleDateFormat(&quot;yyyy/MM/dd HH:mm:ss&quot;);
			DateFormat dateFormatMDY = new SimpleDateFormat(&quot;MM/dd/yyyy HH:mm:ss&quot;);
			Date now = new Date();
			String vDateYMD = dateFormatYMD.format(now);
			String vDateMDY = dateFormatMDY.format(now);
			String vDateMDYSQL =  vDateMDY ;
			java.sql.Date date = new java.sql.Date(0000-00-00);

       requestSQL = &quot;INSERT INTO CREDIT_REQ_TITLE_ORDER (REQUEST_ID,&quot; + 
					&quot; ORDER_DT, FOLLOWUP_DT) &quot; +  &quot;values(?,?,?,)&quot;;


					prs = conn.prepareStatement(requestSQL);

					prs.setInt(1,new Integer(requestID));
					
					prs.setDate(2,date.valueOf(vDateMDYSQL));
					prs.setDate(3,date.valueOf(sqlFollowupDT));


I get this error when the SQL gets executed:

        java.lang.IllegalArgumentException
        at java.sql.Date.valueOf(Date.java:138)
        at com.cmsi.eValuate.TAF.TAFModuleMain.CallTAF(TAFModuleMain.java:1211)


Should I use `setString()` instead with a `to_date()`?

Using setDate in PreparedStatement

While going through some SQL books I found that examples tend to use question marks (`?`) in their queries. What does it represent?

What does a question mark represent in SQL queries?

So I keep hearing that PreparedStatements are good for performance.

We have a Java application in which we use the regular &#39;Statement&#39; more than we use the &#39;PreparedStatement&#39;. While trying to move towards using more PreparedStatements, I am trying to get a more thorough understanding of how PreparedStatements work - on the client side and the server side.

So if we have some typical CRUD operations and update an object repeatedly in the application, does it help to use a PS? I understand that we will have to close the PS every time otherwise it will result in a cursor leak.

So how does it help with performance? Does the driver cache the precompiled statement and give me a copy the next time I do connection.prepareStatement? Or does the DB server help? 

I understand the argument about the security benefits of PreparedStatements and I appreciate the answers below which emphasize it. However I really want to keep this discussion focused on the performance benefits of PreparedStatements.

Update: When I say update data, I really mean more in terms of that method randomly being called several times. I understand the advantage in the answer offered below which asks to re-use the statement inside a loop.
   
   

        // some code blah blah
        update();

        // some more code blah blah 
        update();

    .... 

    public void update () throws SQLException{
     try{
          PreparedStatement ps = connection.prepareStatement(&quot;some sql&quot;);
          ps.setString(1, &quot;foobar1&quot;);
          ps.setString(2, &quot;foobar2&quot;);
          ps.execute();
     }finally {
         ps.close();
         
     }

    }
    


There is no way to actually reuse the &#39;ps&#39; java object and I understand that the actual connection.prepareStatement call is quite expensive. 

Which is what brings me back to the original question. Is this &quot;some sql&quot; PreparedStatement still being cached and reused under the covers that I dont know about?

I should also mention that we support several databases.

Thanks in advance.

All Prepared Statement Solutions on Gang of Coders

Total of 28 Prepared Statement Solutions