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

gmanpertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.