6 Replies Latest reply on Feb 26, 2009 6:52 PM by wilczarz.wilczarz.gmail.com

    SQL injection with getEjbql?

    ido_tamir

      Hi,
      I am using a RESTFul approach and I am rendering a view based on parameters with a subclassed EntityQuery (this works really nice, thanks to SEAM and trinidad, btw). Do I have to fear or how do I prevent an SQL injection attack.


      I guess with an userID (integer) I would be on the safe side, but it would not look as nice.



      @Name("betsListForUser")
      public class BetList extends EntityQuery<Bet> implements IBetList {
           private String userName;
      
           public String getEjbql() {
                String query = "select bet from Bet bet where bet.user.userName = " + "\'" + userName + "\'";
                return query;
           }
      
           @Override
           public Integer getMaxResults() {
                return 10;
           }
      
           public String getUserName() {
                return userName;
           }
      
           public void setUserName(String userName) {
                this.userName = userName;
           }
      
      }


         


      <page view-id="/betList.xhtml">
                 <param name="userName" value="#{betsListForUser.userName}"/>
       </page>
      


        • 1. Re: SQL injection with getEjbql?

          Why don't you use restrictions for this case?

          • 2. Re: SQL injection with getEjbql?
            juanse1987

              public String getEjbql() {
                            String query = "select bet from Bet bet where bet.user.userName =?1 or bet.user.userName=?2 ";
                            query.setParameter(1, userName);
                            query.setParameter(2, AnotheruserName);
                            return query;
                    }




            The EJB QL defines two indexed parameters for the query: ?1 and ?2. These parameters are set with the Query.setParameter( ) method and the query is executed.


            I hope that this works to you!!


            See yaa!

            • 3. Re: SQL injection with getEjbql?
              norman

              Never ever construct a query like this.  Even if you have thought through it and are absolutely certain you aren't vulnerable, it's still a terrible idea.  In addition to the style of parameterized query shown here, you can also use EL in the query.  Seam will automatically turn this into a proper parameterized query.

              • 4. Re: SQL injection with getEjbql?
                wilczarz.wilczarz.gmail.com

                Norman, is it possible to create restriction on enum field with additional option any ? Search forms with such combos are popular design but don't seem to fit EL-based restrictions.

                • 5. Re: SQL injection with getEjbql?
                  norman

                  The EL just creates a parameterized query behind the scenes, so if EJBQL supports parameterization of the part of the query you are interested in, you can use the EL version of it.  If EJBQL doesn't support what you want, then Seam can't help. 

                  • 6. Re: SQL injection with getEjbql?
                    wilczarz.wilczarz.gmail.com

                    I get it - creation of the query comes before the EL resolving. But wouldn't it be useful to have the underlying Query dynamic enough to skip a restriction under some circumstances? E.g. restrictions added as


                    setRestrictions(
                      ConditionalRestriction( "client.foo = #{foo}", "#{not empty foo}" ),
                      ConditionalRestriction( "client.bar is not null", "#{useBar}" ) 
                    )



                    could be used in query assembly only if second expression resolves to true?


                    Sorry for the offtopic :)