Grouper Developers Forum

[Kathryn Huxtable <>]

This is in reference to the Grouper WG call this morning (CST).

If we use prepared statements in the JDBCSourceAdapter we will likely want
to change the syntax of the SQL statements in source.xml. In particular, we
need to change references to %TERM% to a question mark (?).

E.g.

select id,
   concat(firstname, concat(' ', lastname)) as name,
   concat(lastname, concat(', ', firstname)) as lfname,
   lastname, firstname, middlename,
   account.name as loginid,
   department, curriculum, appointment
from individual
   left join account on (account.individualid = id)
   left join faculty on (faculty.individualid = id)
   left join staff on (staff.individualid = id)
   left join student on (student.individualid = id)
where (id='%TERM%')

Becomes

select id,
   concat(firstname, concat(' ', lastname)) as name,
   concat(lastname, concat(', ', firstname)) as lfname,
   lastname, firstname, middlename,
   account.name as loginid,
   department, curriculum, appointment
from individual
   left join account on (account.individualid = id)
   left join faculty on (faculty.individualid = id)
   left join staff on (staff.individualid = id)
   left join student on (student.individualid = id)
where (id=?)

and

select id,
   concat(firstname, concat(' ', lastname)) as name,
   concat(lastname, concat(', ', firstname)) as lfname,
   lastname, firstname, middlename,
   account.name as loginid,
   department, curriculum, appointment
from individual
   left join account on (account.individualid = id)
   left join faculty on (faculty.individualid = id)
   left join staff on (staff.individualid = id)
   left join student on (student.individualid = id)
where (firstname like '%%TERM%%')
   or (lastname like '%%TERM%%')
   or (department like '%%TERM%%')
   or (account.name like '%%TERM%%')

becomes

select id,
   concat(firstname, concat(' ', lastname)) as name,
   concat(lastname, concat(', ', firstname)) as lfname,
   lastname, firstname, middlename,
   account.name as loginid,
   department, curriculum, appointment
from individual
   left join account on (account.individualid = id)
   left join faculty on (faculty.individualid = id)
   left join staff on (staff.individualid = id)
   left join student on (student.individualid = id)
where (firstname like '%'||?||'%')
   or (lastname like '%'||?||'%')
   or (department like '%'||?||'%')
   or (account.name like '%'||?||'%')

This would involve a syntax change in sources.xml for people using the JDBC
connector.

Is this alright for people?

There has also been some suggestion that we use Velocity to do translation
here. There are advantages there, but it would still probably involve a
change in syntax.

I believe that we are better off with the regular JDBC SQL parameter
substitution syntax, because that's what database people know, and if
they're configuring this they will know what to do.

There is also a JNDI parameter substitution mechanism, I believe, that we
could use.

I'm inclined to commit the code I'm attaching because it addresses the most
pressing issues, which are performance of the JDBC connector, and SQL/LDAP
injection in both the JDBC and JNDI connectors.

What do people think?

-K