You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@jackrabbit.apache.org by Behrang Saeedzadeh <be...@gmail.com> on 2014/12/02 01:39:50 UTC

How to escape dynamically generated String values in a JCR SQL2 query?

Let's pretend I have a JCR 2 query string that is made like this:

String sql2Query = "SELECT * FROM [cq:PageContent] " +
                   "WHERE [aProperty] <> \" + aValue + "\"";

Are there any helper methods using which I can escape aValue?

By the way, I already know that in SQL2 we can use placeholders in queries
and let the framework take care of escaping of values for us, but if I were
to create this query dynamically like this, how can I escape aValueto
prevent SQL injection as well as construction of broken queries?

Thanks in advance.


Best regards,
Behrang
http://www.behrang.org

Re: How to escape dynamically generated String values in a JCR SQL2 query?

Posted by Behrang Saeedzadeh <be...@gmail.com>.

Hi Thomas,

Thanks for the reply. Out of curiosity, doesn't Jackrabbit have any helper
classes (e.g. QueryEscapeUtils) that we can use?

Best regards,
Behrang
http://www.behrang.org

On Tue, Dec 2, 2014 at 6:37 PM, Thomas Mueller <mu...@adobe.com> wrote:

> Hi,
>
> Yes, you can use placeholders. Even dynamically created queries can use
> placeholders.
>
> As for SQL-2, you need to use single quotes, not double quotes. Example:
>
>     SELECT * FROM [cq:PageContent]
>     WHERE [aProperty] <> 'Joe''s Taxi'
>
> You only need to escape single quotes, using a single quote escape
> character:
>
>     String aValue = "Joe's Taxi";
>     String sql2Query = "SELECT * FROM [cq:PageContent] " +
>         "WHERE [aProperty] <> '" + aValue.replaceAll("'", "''") + "'";
>
>
> If you want to use XPath, you can use single quotes or double quotes, but
> usually single quotes are used as well (the same as in SQL-2). XPath
> queries don't support placeholders currently.
>
>
> Regards,
> Thomas
>
>
>
> On 02/12/14 01:39, "Behrang Saeedzadeh" <be...@gmail.com> wrote:
>
> >Let's pretend I have a JCR 2 query string that is made like this:
> >
> >String sql2Query = "SELECT * FROM [cq:PageContent] " +
> >                   "WHERE [aProperty] <> \" + aValue + "\"";
> >
> >Are there any helper methods using which I can escape aValue?
> >
> >By the way, I already know that in SQL2 we can use placeholders in queries
> >and let the framework take care of escaping of values for us, but if I
> >were
> >to create this query dynamically like this, how can I escape aValueto
> >prevent SQL injection as well as construction of broken queries?
> >
> >Thanks in advance.
> >
> >
> >Best regards,
> >Behrang
> >http://www.behrang.org
>
>

Re: How to escape dynamically generated String values in a JCR SQL2 query?

Posted by Thomas Mueller <mu...@adobe.com>.

Hi,

Yes, you can use placeholders. Even dynamically created queries can use
placeholders.

As for SQL-2, you need to use single quotes, not double quotes. Example:

    SELECT * FROM [cq:PageContent]
    WHERE [aProperty] <> 'Joe''s Taxi'

You only need to escape single quotes, using a single quote escape
character:

    String aValue = "Joe's Taxi";
    String sql2Query = "SELECT * FROM [cq:PageContent] " +
        "WHERE [aProperty] <> '" + aValue.replaceAll("'", "''") + "'";

If you want to use XPath, you can use single quotes or double quotes, but
usually single quotes are used as well (the same as in SQL-2). XPath
queries don't support placeholders currently.

Regards,
Thomas

On 02/12/14 01:39, "Behrang Saeedzadeh" <be...@gmail.com> wrote:

>Let's pretend I have a JCR 2 query string that is made like this:
>
>String sql2Query = "SELECT * FROM [cq:PageContent] " +
>                   "WHERE [aProperty] <> \" + aValue + "\"";
>
>Are there any helper methods using which I can escape aValue?
>
>By the way, I already know that in SQL2 we can use placeholders in queries
>and let the framework take care of escaping of values for us, but if I
>were
>to create this query dynamically like this, how can I escape aValueto
>prevent SQL injection as well as construction of broken queries?
>
>Thanks in advance.
>
>
>Best regards,
>Behrang
>http://www.behrang.org