• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

request parameter testing

Given the following code:

         <sql:query var="accountInfo" dataSource="${datasource}">
            SELECT DISTINCT sr.account_id
            FROM it_account sp, it_service sr
            WHERE sp.account_id = sr.account_id
               AND (sr.end_date >= '<%=monthFirstDate%>' OR sr.end_date = null)
            <c:if test="${index != ''}">
               AND sr.index_num = <c:out value="'${index}'" escapeXml="false"/>
            </c:if>
            <c:if test="${service != '0'}">
               AND sr.service_type_id = '<c:out value="${service}"/>'
            </c:if>
            <c:if test="${department != 0}">
               AND sp.major_group_id = <c:out value="${department}"/>
            </c:if>
         </sql:query>


What is the proper format that will let me use the ?, such as

         <sql:query var="accountInfo" dataSource="${datasource}">
            SELECT DISTINCT sr.account_id
            FROM it_account sp, it_service sr
            WHERE sp.account_id = sr.account_id
               AND (sr.end_date >= '<%=monthFirstDate%>' OR sr.end_date = null)
            <c:if test="${index != ''}">
               AND sr.index_num = <c:out value="'${index}'" escapeXml="false"/>
            </c:if>
            <c:if test="${service != '0'}">
               AND sr.service_type_id = '<c:out value="${service}"/>'
            </c:if>
            <c:if test= ? }">
               AND sp.major_group_id = ? />
            </c:if>
          <sql:param value="${department}" >
          <sql:param value="${department}" >
         </sql:query>


Need help.  I've done almost no JSP coding but ran into this code which I am trying to make SQL-injection proof, or at least, more proof.

-G

0
gmanpert
Asked:
gmanpert
  • 5
  • 3
5 Solutions
 
mrcoffee365Commented:
What is the error you hit?  Using sql:param is the right thing to do for parameterized queries, and you're doing that.
0
 
gmanpertAuthor Commented:
I know this form is right:

 <sql:query var="accountInfo" dataSource="${datasource}">
            SELECT DISTINCT sr.account_id
            FROM it_account sp, it_service sr
            WHERE sp.account_id = ?
          <sql:param value="${account_id}" >
</sql:query>

What is throwing me off is the if statements.  I am not sure what form is needed to parameterize those:


     <sql:query var="accountInfo" dataSource="${datasource}">
            SELECT DISTINCT sr.account_id
            FROM it_account sp, it_service sr
            WHERE sp.account_id = sr.account_id
               AND (sr.end_date >= '<%=monthFirstDate%>' OR sr.end_date = null)
            <c:if test="${index != ''}">
               AND sr.index_num = <c:out value="'${index}'" escapeXml="false"/>
            </c:if>
                     <sql:param value="${department}" >
          <sql:param value="${department}" >
         </sql:query>

I have no idea who to construct the if portion of this using parameters.  Also, I would like to know if there is another form of the parameterization which does not rely on the "exact order" of the parameters, such as ( my guess):

     <sql:query var="accountInfo" dataSource="${datasource}">
            SELECT DISTINCT sr.account_id
            FROM it_account sp, it_service sr
            WHERE sp.account_id = ?
                 <sql:param value="${account_id}" >
               AND (sr.end_date >= ? OR sr.end_date = null)
                 <sql:param value="${end_date}" >
                AND sr.service_type_id = '<c:out value="${service}"/>'
               AND sp.major_group_id = ? />
                     <sql:param value="${major_group}" >
         </sql:query>

As I mentioned, I've haven't written any lines of JSP code in my life, I am simply looking to modify this script.



0
 
mrcoffee365Commented:
When tags such as sql:query are used, they ultimately produce Java code.  The tags have an expected syntax, and you cannot provide the wrong syntax (as in your last example).

The sql:param values have to be in order.  I don't know of another way to specify them, because the "?" indicators in the query have no identifiers other than order.

Again -- are you hitting an error with your c:if statements, and if so, what is the error?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mrcoffee365Commented:
Actually, I just realized that you put your c:if statements in the middle of your sql:query tag.  I'm pretty sure you can't do that.

If you want to create 2 different SQL queries, then the best method might be to create 2 different queries, then invoke them based on your c:if statements.
0
 
gmanpertAuthor Commented:
Ok, let me start again.

Here are the relevant pieces of code as they currently exist in the program, which works!

<%
   String begdate = "", enddate = "";
   if (!Utilities.isEmpty(request.getParameter("begdate")))
      begdate = getDate(request.getParameter("begdate"));
   if (!Utilities.isEmpty(request.getParameter("enddate")))
      enddate = getDate(request.getParameter("enddate"));
 %>

 <c:choose>
   <c:when test="${empty param.begdate}">
      <c:set var="begdate" value=""/>
      <c:out value="Beginning Date: unspecified"/><br/>
   </c:when>
   <c:otherwise>
      <c:set var="begdate"><%=begdate%></c:set>
      <c:out value="Beginning Date: ${param.begdate}"/><br/>
   </c:otherwise>
 </c:choose>

   <sql:query var="charge_info" dataSource="${datasource}">
      SELECT smg.name AS dept, sa.emp_id
      FROM it_account sa, it_charge_history sch
      WHERE sa.account_id = sch.account_id
      AND sch.journal_no = sa.journal_no
      <c:if test="${begdate != ''}">
      AND sch.effective_date >= '<%param.begdate}%>'
      </c:if>
   </sql:query>

That's what the program currently has, and it works.  All I want to know is how to make this structure:

      <c:if test="${begdate != ''}">
      AND sch.effective_date >= '<%param.begdate}%>'
      </c:if>

work with parameters.  I suspect it should be something like this:

      <c:if test="${begdate != ''}">
      AND sch.effective_date >= ?
      </c:if>
    <sql:param value="${begdate}" />
   </sql:query>

 but I am not familiar enough with JSP or JSP syntax to know.  Also, I'm not sure if the if statement will still work, or if I need to substitute another expression in?

Any ideas?  

Now that I've figured out where the errors are showing up, I'll post the next one I run into.






0
 
mrcoffee365Commented:
>> but I am not familiar enough with JSP or JSP syntax to know.  Also, I'm not sure if the if statement will still work, or if I need to substitute another expression in?

Are you able to run the program with your changes to the code?  That would let you know whether your changes are working.

You will hit at least one problem -- you didn't carry over the single quotes from the parameter in the working code to your sql:param value.  So you'd want something like:
<sql:param value="'${begdate}'" />

But you'll know more if you try to run your changed code.  
0
 
mrcoffee365Commented:
I did test the basic id, of putting a c:if within the sql:query param, and setting the sql:param within the if, and that worked.  So it's possible to do, although I'm not sure that the logic you're using will do precisely what you want.  It would be best if you could try it with your data to see if the outcomes are what you want.
0
 
gmanpertAuthor Commented:
After extensive testing, this is what worked:

 <sql:query var="charge_info" dataSource="${datasource}">
      SELECT smg.name AS dept, sa.emp_id
      FROM it_account sa, it_charge_history sch
      WHERE sa.account_id = sch.account_id
      AND sch.journal_no = sa.journal_no
      <c:if test="${begdate != ' '}">
      AND sch.effective_date >= ?
      </c:if>
    <sql:param value="${begdate}"/>
   </sql:query>
   

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now