Re: Escaping Strings

--------------090600090004030805030808
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit



On 07/11/2016 11:34 PM, Tim Gustafson wrote:
> Hello,
>
> I'm trying to build a query builder, similar to the one implemented in
> PHP in the Drupal project.  This requires that I be able to reliably
> escape MySQL identifiers (tables, columns, etc) and column values.
> However, there does not appear to be any methods exposed from the
> MySQL Java connector to do this.
>
> I know PerparedStatements are the way to go for SQL, and I use
> PreparedStatements whenever possible, but they just don't work for
> query builders.

Hi,

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 know there are tools like JOOQ that do query building, but I want to
> build something that is very syntactically similar to the Drupal query
> builder, and JOOQ (and other projects like it) seem pretty far off
> that mark.

I don't see why it wouldn't be possible to do it and still use 
PreparedStatements.
>
> Does the MySQL Java driver not expose mysql_real_escape_string, and is
> there no equivalent for safely escape identifiers like table names and
> column names?

No it does not, because it hasn't historically been needed. As far as 
identifier names, there is no function in any MySQL driver that does 
that, because it's relatively simple to do using backticks (or double 
quotes), and the rules for identifiers are not the same as string 
literals (which is what mysql_real_escape_string handles), see 
https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

   -Mark

--------------090600090004030805030808--