jakarta-taglibs-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hans Bergsten <h...@gefionsoftware.com>
Subject Re: Startard Taglib- SQL bind variables
Date Thu, 23 May 2002 19:40:26 GMT
Steve A. Olson wrote:
> I'd like to propose an alternate syntax for binding variables to SQL
> statements in the Standard SQL taglib.
> 
> 1- Instead of using nested "param" tags that match a corresponding "?"
> placeholder, allow developers to directly embed variables into the sql
> string.  Depending on the complexity of the parsing it may be easier to use
> "colon" variables instead of "$" variables-- I'm not sure about that.
> 
> 2- Additionally, I'd like to propose an "sql" attribute that could be used
> in-lieu of the tag body; again, for reducing extraneous text.
> 
> Here is an example of the proposed syntax:
> 
>    <sql:update>
>     UPDATE account
>     SET Balance = Balance - $transferAmount
>     WHERE accountNo = $accountFrom
>    </sql:update>
> 
> Again, using the sql attribute:
> 
>    <sql:update
>         sql="UPDATE account
>              SET Balance = Balance - $transferAmount
>              WHERE accountNo = $accountFrom"
>         />
> 
> *BTW, I'm not sure if line breaks are allowed within attribute assignments,
> however, it makes for more readable code.

This can already be done (but is dangerous, as described later). With
the correct syntax, it looks like this:

     <sql:update
          sql="UPDATE account
               SET Balance = Balance - ${transferAmount}
               WHERE accountNo = ${accountFrom}"
          />

Any JSTL action attribute that accepts EL expressions can be set to
a combination of EL expressions and static text, as above.

It's dangerous to build the SQL statement from static text and
variables, though. For instance, if the user can provide the value
for the variables (e.g. through request parameters), a value like
"1234 OR 1 = 1 --" for the accountFrom variable will cause all
accounts to be changed. When you explicitly assigns a value to a
placeholder, you don't have to worry about this type of problem.

> Again, using current syntax:
> 
>   <sql:update>
>     UPDATE account
>     SET Balance = Balance - ?
>     WHERE accountNo = ?
>     <sql:param value="$transferAmount">
>     <sql:param value="$accountFrom">
>   </sql:update>
> 
> Finally, with this simplified syntax, we can collapse all of the tags
> required to implement a prepared query into a single concise tag as follows!
> (I find this syntax much more usable and far less "wordy".)
> 
>   <sql:query
>        var="q"
>        sql="select balance from account where accountNo = $accountFrom">
>     <%-- body processed for each fetch in resultSet ... --%>
>   </sql:query>

This is more a question of preference, and arguably about consistency.
In JSTL, we tried to provide actions that does one thing well instead
of multiple things: <sql:query> gets data from a database and <c:forEach>
loops through any type of collection. To do the above, you just combine
them:

   <sql:query
     var="q"
     sql="select balance from account where accountNo = $accountFrom" />
   <c:forEach items="${q.rows}" var="current">
     <%-- body processed for each fetch in resultSet ... --%>
   </c:forEach>

Hans
-- 
Hans Bergsten		hans@gefionsoftware.com
Gefion Software		http://www.gefionsoftware.com
JavaServer Pages	http://TheJSPBook.com


--
To unsubscribe, e-mail:   <mailto:taglibs-dev-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:taglibs-dev-help@jakarta.apache.org>


Mime
View raw message