6

I am aware that using Prepared Statements is the best way to protect against SQL Injection (and syntax errors due to unescaped characters in unchecked input).

My current situation is that I am writing some Java code to move data from one third party application to another. The destination application uses a proprietary version of Sybase and so whilst I do have the JTDS JDBC driver PreparedStatement fails, as the driver uses temporary stored procedures which aren't supported in this particular flavour of the database. So I only have Statement to work with and I have no control over the user input as it is coming from another application.

There is this similar question but that is focused on fixing the problem where you have a parameter such as a table which cannot be handled via a Prepared Statement. My case is different and hopefully simpler, since I have straightforward SQL statements. I would like to know if there is a best practice for replicating something like the following without using PreparedStatement:

PreparedStatement statement = connection.prepareStatement("UPDATE mytable SET value=? WHERE id=?");
statement.setInt(1, getID());
statement.setString(2,userInput);
statement.executeUpdate();

So I guess the problem is how can I sanitise the user input reliably? I can try to do that myself from scratch but this seems like a bad idea as there is likely to be at least one edge case I'd miss, so I was hoping there was a library out there that would do that for me, but I haven't been able to find one so far.

Community
  • 1
  • 1
David Webb
  • 190,537
  • 57
  • 313
  • 299
  • I dont think there is a way you can make PrepareStmt syntax with statement. Only way I guess is sanitizing input and use Statement. May be OWASP libraries help you. – kosa Nov 07 '12 at 15:19
  • @Nambari - Yes, so I guess my question is how do I sanitise the input reliably? I have clarified this in the question. – David Webb Nov 07 '12 at 15:20
  • 4
    The reason why this is done using `PreparedStatement` is because sanitising input works differently for every database – the concern is left to the database driver which knows best, and can transport query parameters without having to stringify them at all. Any library dealing with this externally would have to include specific code for every database it supports; and be, by definition incomplete. If such a library exists, it's fantastically unlikely it supports a flaky proprietary DB. – millimoose Nov 07 '12 at 15:22
  • @DaveWebb: I am sort of agree with millimoose comment. I don't think there is any library available out of box. You may need to develop one based on best practices described on sites like OWASP. – kosa Nov 07 '12 at 15:24
  • Check out this stackoverflow topic http://stackoverflow.com/questions/129677/whats-the-best-method-for-sanitizing-user-input-with-php – Tri Nov 07 '12 at 15:25
  • 2
    tl;dr: you're probably hosed. Write your sanitiser code, and favour whitelisting wherever possible. (I.e. only accept digits when the parameter is numeric, only accept alphanumerics for most text, etc.) This will let you avoid having to pore over every inch of documentation to make sure you catch all the possible escape sequences. – millimoose Nov 07 '12 at 15:25
  • @millimoose - My problem is that the user input is a problem resolution field in a third party product so I can't restrict the input and it's also likely to contain all sorts of horrible characters. My code is just trying to move data from one app to another. I do see your point about this not really being a generic SQL problem though, but the thought of writing my own sanitising code just feels wrong. – David Webb Nov 07 '12 at 15:27
  • 1
    @DaveWebb You could try and look at the classes in the database driver to see if there aren't any sanitisation / escaping utilities. – millimoose Nov 07 '12 at 15:28
  • @millimoose - I was slightly misleading with the question. The driver supports PreparedStatement but not with this particular weird flavour of the database that's being used. So I can create the `PreparedStatement`, but it just fails to execute. The driver uses temporary stored procedures which aren't supported by the DB I'm connecting to. – David Webb Nov 07 '12 at 15:30
  • @millimoose - I think your solution of whitelisting and just dropping any characters I don't like the look of is best. It's only a description so if it loses a few characters here and there it's not the end of the world. – David Webb Nov 07 '12 at 15:50
  • @DaveWebb Just remember that you'll probably still have to accept a few that need escaping. (E.g. you might not want to strip apostrophes, or backslashes if the descriptions can contain Windows filenames.) – millimoose Nov 07 '12 at 15:58
  • @millimoose - just having that discussion now. Slightly worried the problem resolutions might contain SQL statements. :-) Quotes we're definitely passing on, other special characters are probably for the bit bucket. – David Webb Nov 07 '12 at 16:05
  • 2
    @DaveWebb I noticed you updated your question to mention it's a Sybase server. I looked at the jTDS source, and it seems to contain some code on stringifying parameters, it's the method `Support.embedData()`. (It only seems to escape `'`.) It also hints towards there being multiple methods of executing prepared statements, not just the temporary stored proc. (Look at the constants near `TdsCore.TEMPORARY_STORED_PROCEDURES`.) It might be worth poring over the docs more closely to see if this can be configured, and if your database version supports any of the other methods. – millimoose Nov 07 '12 at 16:16
  • Can't you just use the Sybase JDBC driver? – Mark Rotteveel Nov 07 '12 at 19:29

2 Answers2

2

The ESAPI library has procedures for escaping input for SQL and for developing your own db specific encoders if necessary.

Chris Nava
  • 6,614
  • 3
  • 25
  • 31
1

Check out JTDS FAQ - I'm pretty confident that with a combination of properties prepareSQL and maxStatements you could go there (or "could have gone" as you probably completed that task years ago :-) )

Jan
  • 13,738
  • 3
  • 30
  • 55