To get some data I'm creating an sql query :)
Of course there's some filtering and ordering parts.

To get the result I use "NamedParameterJdbcTemplate" and when I need to add something to the "where" part, I use parameter map, to prevent injection.

But it's different with "order by" part, as there is no automatic escaping (and it's a part of sql). This order part is sometimes filled with data from user (directly), sometimes put some extra sort parameters from inside code. There is one problem: sometimes this sort field contains not only column name, but a sql statement.

Now each parameter for sorting is escaped manually by replacing some characters (like ') to empty string, but some parameters we set for our code is a bit complex to pass this rule.

What is best way to prevent sql injections in sort part of query, when you use jdbc template?

To help guard against SQL injection on the database side, have a look at the DBMS_ASSERT built-in Oracle package: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_assert.htm

You might find the SIMPLE_SQL_NAME function will help protect against SQL Injection for your ORDER BY clause.

Hope it helps...

Generally speaking ... should join tables (i.e. associative tables) be created as Index Organized Tables (Oracle) , Clustered Indexes (SQL Server) .... or plain old heap tables (with separate indexes on the 2 columns).

The way I see if, the advantages are:

Speed improvement. You're avoiding a heap table look up.

Space Improvement. You're eliminating the heap table altogether, so you're probably saving ~30% space.

The disadvantages:

Index Skip Scan (only applies to Oracle) .. will be faster then a Full Table Scan, but slower then an Index Scan. So searches on the second column of the compound key will be slightly slower (Oracle), much slower (MSSQL).

A Full Index Scan will be slower then a Full Table Scan - so if most of the time the Cost Based Optimizer is doing Hash Joins (which don't take advantage of Indexes) ... you could expect worse performance. (Assuming that the RDBMS doesn't first filter the tables).

Which makes me question whether any type of indexes are really required for Join Tables, if you're predominately going to be doing Hash Joins.

My personal rule-of-thumb is to create two-table associative entities as index-organized-tables, with the primary key constraint being the access "direction" I expect to be more commonly used. I'll then generally add a unique index to cover reverse order of the keys, so in all cases the optimizer should be able to use unique-scan or range-scan access.

Three-table (or more) associative entities generally require significantly more analysis.

Also, the optimizer will use indexes with hash join operations; generally fast full scans, but indexes nonetheless.

I've got a new database here and it's an upgraded version from Oracle 10g to Oracle 11g - the main problem is with LOB columns and everytime any function returns a LOB as result, the new database won't return strings like the old one did:

Old DB:

["C"]=>
string(23) "3874163,3874197,3874201"

New DB:

["C"]=>
resource(182) of type (stream)

Now when reading the streams sometimes there is an error of a non-existing stream resource beeing referenced and everything fails. I'm guessing the connection closed in the meantime without the stream beeing read and therefore the access is lost.

When changing the statements to include a casting against varchar for example:

CONVERT(VARCHAR, C, 120)

Or like this:

SELECT TO_CHAR(FUNC())

The value is returned as a string again but this is not really an optimal solution as every statement would need to be changed.

Is there any way/option to prevent LOBs from beeing delivered as streams so they are instead delivered as strings like in Oracle 10g?

Edit:
We are using the oci function-set for db access.

Not really an answer as such but a few items that I hope helps.

It looks like that there is a small difference in the way that LOBs are returned between 10g and 11g, under 11g there is some notes about the conversion from btyes to byteStreams when LOBs are over a certain value, in the JDBC reference manual (I understand that this doesnt effect OCI calls as they use a different driver set).

From what I can see in terms of the OCI8 functions within php the default operation of the fetch functions is that the LOBs are returns as a reference and need to be accessed using the ->read() ->load() etc functions (see http://au.php.net/oci_fetch_array - regarding the mode and the default).

Now I dont know if you are using the OCI functions to access your oracle system as it's not specified in your question.

Couple of other items that would help in figuring this out would be if you could let us know if you recompiled php or updated the oracle drivers with the newer client version at all.

I know its not a full solution but if you are using oci_fetch_* to return the row, add a second argument to the call of OCI_RETURN_LOBS, this will cause the fetch to return a string of the LOB field instead of a reference to a stream, or use the $variable["C"]->load() to access this LOB this will cause it to load the full stream and act like a normal string.

Hope this helps.

I have this complicated SQL query for Oracle that I want to visualize in a diagram to make it understandable for my co-workers. I tried at http://snowflakejoins.com but it just chokes on it.

Has someone a better suggestion? I prefer a web-app on the internet and if not a desktop app for windows.

with 
  logs as (
    select 
      l.job_id, 
      l.subjob,
      sum(l.verwerkt) verwerkt, 
      sum(l.errors) errors, 
      max(l.datum) laatst
     from 
      dinf_monitor_logs l, 
      dinf_monitor_jobs j 
     where 
      l.datum>sysdate-j.dagen
      and j.job_id=l.job_id(+)
     group by 
      l.job_id, 
      l.subjob
  ),
  alllogs as (
    select job_id, subjob, max(datum) laatst from dinf_monitor_logs group by job_id, subjob
  )
  select row_number() over(order by alllogs.job_id, alllogs.subjob) r,
    alllogs.job_id,
    alljobs.naam,
    alllogs.subjob,
    logs.verwerkt, 
    logs.errors, 
    alllogs.laatst datum,
    alljobs.wikilink,
    alljobs.loglink,
    alljobs.contact,
    case 
      when alllogs.laatst is null then 1
      when round(sysdate-(alllogs.laatst+alljobs.dagen))<0 then 0
      else round(sysdate-(alllogs.laatst+alljobs.dagen))
    end overtijd,
    case 
      when logs.errors-alljobs.max_errors>0 then 5
      when logs.verwerkt-alljobs.min_verwerkt<0 then 7
      when round(sysdate-(alllogs.laatst+alljobs.dagen))>0 then 3
      else 11
    end status
  from logs, alllogs, (select job_id, naam, wikilink, loglink, contact, dagen, min_verwerkt, max_errors from dinf_monitor_jobs) alljobs
  where 
    logs.job_id(+)=alllogs.job_id 
    and logs.subjob(+)=alllogs.subjob
    and alllogs.job_id=alljobs.job_id
  order by alllogs.job_id, alllogs.subjob

You can use the "Query Builder" tab of the Oracle's SQL Developer.

The result of your sample query will be:

Will the following two queries be executed in the same way?

SELECT COUNT(*) from person ORDER BY last_name;

and

SELECT COUNT(*) from person;

Either way they should display the same results, so I was curious if the ORDER BY just gets ignored.

The reason I am asking is because I am displaying a paginated table where I will get 20 records at a time from the database and then firing a second query that counts the total number of records. I want to know if I should use the same criteria that the first query used, or if I should be removing all sorting from the criteria?

According to the execution plan, the two queries are different. For example, the query:

select count(*) from USER

Will give me:

INDEX (FAST FULL SCAN)  3.0 3   453812  3457        1   TPMDBO  USER_PK FAST FULL SCAN  INDEX (UNIQUE)  ANALYZED

As you can see, we hit USER_PK which is the primary key of that table.

If I sort by a non-indexed column:

select count(*) from USER ORDER BY FIRSTNAME --No Index on FIRSTNAME

I'll get:

TABLE ACCESS (FULL) 19.0    19  1124488 3457    24199   1   TPMDBO  USER    FULL    TABLE   ANALYZED    1

Meaning we did a full table scan (MUCH higher node cost)

If I sort by the primary key (which is already index,) Oracle is smart enough to use the index to do that sort:

INDEX (FAST FULL SCAN)  3.0 3   453812  3457    13828   1   TPMDBO  USER_PK FAST FULL SCAN  INDEX (UNIQUE)  ANALYZED

Which looks very similar to the first execution plan.

So, the answer to your question is absolutely not - they are not the same. However, ordering by an index that Oracle is already seeking anyway will probably result in the same query plan.