2

I create PreparedStatement when I need to pass arguments to the answers there do not address your problem, please edit to explain in detail the parts of your question that are unique. Title

Is it worth to create a PreparedStatement for int values? SQL queries, but is it worth to prepare a statement to pass int arguments and to be closed after the execution?

void delete(int key, int orElse) throws SQLException
{
    try(PreparedStatement pst = this.connection.prepareStatement(
        "DELETE FROM a_table WHERE the_int_primary_key=? OR random_int_field=?"
    ))
    {
        pst.setInt(1, key);
        pst.setInt(2, orElse);
        pst.executeUpdate();
    }
}

Is it worth to prepare that statement? Is it going to increase the security in anyway?

What if I do that with a normal statement? Is it risky in any way? Will it execute a bit faster?

void delete(int key, int orElse) throws SQLException
{
  try(Statement stm = this.connection.createStatement())
  {
    stm.executeUpdate(
      "DELETE FROM a_table WHERE the_int_primary_key="+key+" OR random_int_field="+orElse
    );
  }
}

Edit: This question is not duplicated of Do prepared statements slow down program conspicuously? because:

  • The other question plains to reuse the prepared statement multiple times, I plan to use it only once, the documentation already specifies that it's faster to reuse PreparedStatements
  • I'm planning to use the statement only for ints and I'm worried about SQL Injections but at the same time I'm not sure if it's possible to inject SQL with primitive int parameters, the micro speed enhancement would be just a small plus, I'm not asking just because of performance. The other question only wants to speed it up and may be using strings, dates, or other non-primitive types.
Community
  • 1
  • 1
Polyana Fontes
  • 3,156
  • 1
  • 27
  • 41
  • always use a prepared statement or you do risk a sql injection attack – Rabbit Guy Jul 28 '16 at 21:07
  • 3
    Always. **Always**. ALWAYS use a `PreparedStatement`. No exceptions. No excuses. – Boris the Spider Jul 28 '16 at 21:08
  • Possible duplicate of [Do prepared statements slow down program conspicuously?](http://stackoverflow.com/questions/3589961/do-prepared-statements-slow-down-program-conspicuously) – 4castle Jul 28 '16 at 21:09
  • 1
    "_so you **do** risk a sql injection attack_" @blahfunk? Ha. – Boris the Spider Jul 28 '16 at 21:10
  • @4castle That's a very different question – Polyana Fontes Jul 28 '16 at 21:13
  • @JoséRobertoAraújoJúnior All that matters is that the answers answer your question. – 4castle Jul 28 '16 at 21:13
  • 2
    Consider re-thinking your question as: (1) Is it really that much more trouble to use a `PreparedStatement` instead of creating the dynamic SQL for a `Statement`? (2) Do you know with absolute certainly that you will only ever be working with integer values, never strings or dates? (3) Is there reason to expect that there would be a *significant* performance benefit from using a `Statement` with dynamic SQL? Why? – Gord Thompson Jul 28 '16 at 21:13
  • 4
    Yes. It is worth it. And you'll thank yourself the day you'll introduce a `String` parameter. – Tunaki Jul 28 '16 at 21:15
  • I edited the question to show why it's a different question from http://stackoverflow.com/questions/3589961/do-prepared-statements-slow-down-program-conspicuously – Polyana Fontes Jul 28 '16 at 21:33

2 Answers2

7

From java docs:

A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

To answer your question: Yes very worth it, it's important to use a prepared statment, it's the best way to protect you against injection attacks(like sql injection), a normal statement will do nothing to protect against these types of attacks, even if you make your very own "good" sql parser it will presumably fail to protect against some attacks.

Gherbi Hicham
  • 2,416
  • 4
  • 26
  • 41
  • Except that you can't store SQL in an int... so there's no security concern here at all. Only speed concerns and idiot proofing. – 4castle Jul 28 '16 at 21:11
  • 3
    The concern is that someone could find a way, maybe... who knows? a preparedstatement guards against it. It's like saying you don't have to wear a safety belt until you get to the street because there's no risk of getting hit in the driveway. No, you could. And buckling up doens't cost must for the safety it profides. Same thing for a prepared statement – Rabbit Guy Jul 28 '16 at 21:13
  • 2
    Additionally it's a terrible idea to start questioning whether you need them or not, because one day you'll overlook something and get it wrong - it's inevitable. If you always use them, you can't make a mistake like that. – James Jul 28 '16 at 21:14
  • @4castle Idiot proofing is great sometimes, because you never know when some hacker can suddenlly hack into your app because you were lazy enough to not use a protective measure. – Gherbi Hicham Jul 28 '16 at 21:21
  • But, how could someone inject SQL on primitive int? – Polyana Fontes Jul 28 '16 at 21:36
  • 1
    I liked your comment @blahfunk – Polyana Fontes Jul 28 '16 at 21:39
  • @GherbiHicham If someone was able to hack into the app, then they would just replace your `PreparedStatement` class or database drivers with whatever they wanted. No amount of injection proofing can protect from code modifications. – 4castle Jul 28 '16 at 21:41
  • 2
    It is not an question of "inject SQL on a primitive int". It is more a question of "could this code be copied/pasted into another area, or used as an example, and subsequently modified to use string parameters." If it is at all possible (ie, always), then using a `PreparedStatement` **here** will automatically mean a `PreparedStatement` will be used **there**, and you've gained safety. – AJNeufeld Jul 28 '16 at 21:41
  • @AJNeufeld Yes it's always good to include the possibility of working with people, and having a code that's "maintainable", you never know when someone can use that piece of code /method and just makes a disaster. – Gherbi Hicham Jul 28 '16 at 21:58
  • 1
    That's a nice point, I hadn't thought about other people. So I'll use `PreparedStatement` – Polyana Fontes Jul 28 '16 at 22:27
1

Ask instead: "Is is more complicated to use prepared statement?". Using nothing but plain JDBC, it's a tiny bit longer. So there's about nothing to gain. So don't take any risk (SQL injection) and don't mix plain (unprepared) statements in.

In case you find it too verbose, then look for a library providing better syntax or maybe write yourself a utility allowing things like

try (MyPreparedStatement pst = new MyPreparedStatement(connection, 
    "DELETE FROM a_table WHERE the_int_primary_key=? OR random_int_field=?"
))
{
    pst.executeUpdate(1, 2);
}
maaartinus
  • 44,714
  • 32
  • 161
  • 320