Re: Escaping Strings

On 07/12/2016 05:44 PM, Tim Gustafson wrote:
>> Seems to me, looking at Drupal's API for a query builder, that prepared
>> statements would work just fine? The user is specifying very parameterized
>> variants of the places where one would use placeholders in a
>> PreparedStatement, i.e.
>>
>> |$query->condition('u.uid', 0, '<>'); |
>>
>>
>> would be "WHERE u.uid <> ?", pstmt.setParameter(n, 0). It would be
>> straightforward if creating a similar fluent API that an instance of this
>> "condition" could track it's position in the generated query, contribute the
>> SQL fragment that includes the placeholder, and upon execution set the
>> parameter value.
> I think you're not seeing the whole picture.  Here's a pseudo-code
> example of the kind of flexibility I need.  Imagine here that we're
> constructing a query where users can look up other users by first or
> last name.  Imagine SelectQuery is my query builder class.
>
> <code>
>
> public void doGet(HttpServletRequest request, HttpServletResponse response) {
>    SelectQuery select = new SelectQuery("users");
>
>    String firstName = request.getParameter("firstName");
>    String lastName = request.getParameter("lastName");
>
>    if (firstName != null) {
>      select.condition(
>        "user.firstName",
>        "%" + firstName.trim() + "%",
>        "LIKE"
>      );
>    }
[snip]

Hi Tim,

Nope, I'm seeing the whole picture. I think you're missing how the 
Builder pattern is implemented in an object-oriented way.

Imagine Select is implemented something like this:

public class Select {
     class Condition {
         private String leftHandSide;
         private Object value;
         private String operator;
     }

     String table;
     List<Condition> conditions = Lists.newLinkedList<Condition>();
     List<String> columns = Lists.newLinkedList<String>();

     // extra credit to add fluent methods to change this into AND vs OR

     public Select condition(String leftHandSide, Object value, String 
operator /* or ideally something more type-safe) {
         Condition cond = new Condition();
         cond.leftHandSide = leftHandSide;
         cond.value = value;
         cond.operator = operator;

         return this;
     }

     public ResultSet execute() {
         StringBuilder actualQuery = new StringBuilder("SELECT ");

         // append columns, separated by ,

         actualQuery.append(" FROM ");
         actualQuery.append(escapeIdentifier(table));

         if (conditions.size() > 0) {
             boolean needsAnd = false;

             for (Condition cond : conditions) {
                 if (needsAnd) {
                     actualQuery.append(" AND ");
                 } else {
                     needsAnd = true;
                 }

                 actualQuery.append(cond.leftHandSide);
                 actualQuery.append(" ");
                 actualQuery.append(cond.operator);
                 actualQuery.append(" ? ");
             }
         }

         // .. do ORDER BY in a similar fashion

         String sql = actualQuery.toString();
         PreparedStatement pStmt = connection.prepareStatement(sql);

         if (conditions.size() > 0) {
             int paramNum = 1; // JDBC starts numbering with 1

             for (Condition cond : conditions) {
                 pStmt.setObject(paramNum, cond.value);
             }
         }

         return pStmt.executeQuery();
     }
}

Obviously pseudocode, and not cleaning up resources, etc, but it should 
be enough to understand the concept of a "builder" storing away the 
"configuration" of the to-be-built object instance, and then using that 
knowledge to build it (in this case execute() it) in a deferred fashion 
so that all of the various constraints the user has placed on it can be 
considered. There is no reason this couldn't be extended to "understand" 
tables with joins, subqueries, etc.

>
>    select.orderBy("lastName", "asc");
>    select.orderBy("firstName", "desc");
>
>    ResultSet results = select.execute();
>
>    // some more code to display the results back to the user
> }
>
> </code>
>
> In this case, parameterized SQL won't work because you don't know
> ahead of time if you are selecting based on one column, two columns,
> or no columns.  And this is a very simple example.  Some of my Drupal
> query builder instances are very complicated and include lots of logic
> around user security and preferences, not to mention joins to other
> tables each with their own "where" clauses and so on.  You would have
> to have a zillion different prepared statements lying around to
> account for all the possible combinations of input parameters,
> security restrictions and user preferences.

This is the bread-and-butter of ORMs like Hibernate and JPA and 
near-ORMs like myBatis, they all do it the above way, or in a similar 
fashion. If you'd like to reinvent the wheel, it's worth following their 
lead. The whole idea is the builder code is responsible for building the 
"zillions of prepared statements" for the user, removing boilerplate, 
reducing complexity and ensuring safe use of the underlying more "raw" API.

Regards,

    -Mark

-- 
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe:    http://lists.mysql.com/java