Re: SQL Injection with JDBC

Arne Vajhøj wrote:
Lothar Kimmeringer wrote:
Arne Vajhøj wrote:
Lothar Kimmeringer wrote:
There is nothing special about it. In general you create
the statement using one or more StringBuffers (one for the
where-clause, one for the list of selected tables, other
for more stuff like grouping etc.) and a list of parameters.

At the end you create a PreparedStatement, set the sql-statement
as you built it up with your StringBuffer(s) and set the
parameters you find in the list.
No. That is not a very common scenario.

Just because it doesn't happen in your world very often, doesn't
mean that it doesn't happen anywhere or that it's uncommon.

No.

But you can either take my word for it or do some research
on it.

> Again,
how do you implement a search as it is provided at e.g.
http://www.google.com/advanced_search?hl=en

Answered in another reply.

Most database frameworks cache PreparedStatements with
fixed SQL instead of building the SQL dynamicly.

When talking about a database framework in Java, the most common
is JDBC which is just a bunch of interfaces and a Factory-class,
so what is happening there is completely up to the implementor
of the JDBC-driver of a database.

If you read database framework in what I wrote as being at the
JDBC level, then I have a communication problem.

Arne's experience jives with mine. None of the examples I've read, neither in tutorials nor from actual systems, nor real-world code in dozens of Java projects on which I've worked, build up PreparedStatements with dynamic SQL. They all relied on fixed SQL statements with parameters for the run-time part. At most, they may have included clauses dynamically (e.g., "AND t.foo=?"), but in my own experience that type of code is very messy to maintain, and one winds up selecting entire query strings based on user input, not building the strings up piecemeal.

I also concur with the definition of "database framework" not meaning JDBC as such, but the code that rests atop the JDBC layer, i.e., the project-specific middleware.

It seems that you two are talking at cross purposes using different definitions. Perhaps by avoiding terms with controversial definitions we can achieve a common understanding.

The thing that Arne (and I) say "should not be used in real-world projects" is non-parametrized, non-prepared statements where the SQL string is built up entirely in text then executed as such. This is subject to things like SQL-injection attacks and wrongly-typed values. The thing that we recommend is the use of PreparedStatement to embed type-safe parameters into SQL statements that are not subject to such flaws. PreparedStatement is perfectly capable of handling the kind of "googley" queries mentioned without the dangers of non-parametrized queries.

I venture to say that the referenced Google link is very unlikely to have dynamic SQL (as the term is defined upthread and used by Arne), but depends entirely on parametrized queries with fixed (i.e., hard-coded) strings for the non-parameter parts of the query. Given the level of expertise at Google (for example, Joshua Bloch) that guides their development practices, it would be shocking if they did not use PreparedStatements with parameters for the user inputs.

--
Lew
.

Relevant Pages

  • Re: 3vl 2vl and NULL
    ... "strings" specifically are so interesting to you. ... input that can be cast to a numeric type, ... but it is not expected that the DBMS is forcing you to do so. ... It seems you can't accommodate the SQL outcomes because it doesn't have a "shape" that you are comfortable with. ...
    (comp.databases.theory)
  • Re: Performance Problem with Stored Procedure calls to Sql Server
    ... Look at this in SQL profiler. ... The strings contain XML. ... > Dim oRs As ADODB.Recordset ...
    (microsoft.public.data.oledb)
  • Re: Linq; expression parser?
    ... object based query at runtime and had to revert to stored strings. ... but if I had the need to write dynamic SQL inside the ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: 3vl 2vl and NULL
    ... and the SQL view backing that screen has many instances of this same ID value, then the data models are different. ... To answer David's question too, if you were to prepare a diagram of your choice, perhaps UML these days, of the logical data model that a software application needs for interfacing with the database and the logical model it needs for the UI, choosing the same entities and attributes to model, your UML diagrams would be different. ... The RDBMS takes those relations, validates with constraint logic likely written in a different language than the other validations, and turns the data into strings to be stored. ... DBMS to the Screen App to be exclusively 1NF in the strictest sense. ...
    (comp.databases.theory)
  • Re: Statement and Resultset
    ... strings. ... protection against SQL injection security hacks. ... So how does one avoid the overhead of a PreparedStatement when Statement incurs it anyway? ... Moreover it is a more complex resource, as a PreparedStatement must be able to replace the ?'s with passed parameters. ...
    (comp.lang.java.programmer)