cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Zeigler <robert.zeig...@gmail.com>
Subject Re: Apostrophe escaping in select
Date Thu, 12 Feb 2009 05:08:42 GMT
Two comments.
First, for the query you're running, you probably want an object  
select query, rather than SQLTemplate:

SelectQuery q = new SelectQuery(Person.class);

Expression qual =  
ExpressionFactory.matchExp(Person.COUNTRY_PROPERTY,country);
qual =  
qual.andExp(ExpressionFactory.matchExp(Person.NAME_PROPERTY,name);

q.setQualifier(qual);
dataContext().performQuery(qual);

I typically use SQLTemplate for performance reasons, or to express  
some query that doesn't necessarily correlate to a particular object  
in the db (aggregrate-function queries, like sums, counts, etc.).   
Otherwise, I stick with object-select queries.  You could even create  
the above object select query in the modeler and just reference it by  
name in the code:

dataContext.performQuery("PersonSelectQuery",params);//2.0.4 only; for  
3.0, you would used a NamedQuery object; params is a map with the  
appropriate parameters, as below.

But if you really want to use SQLTemplate, then you should do two  
things:
   1) use #result to describe the results that cayenne should expect  
from your query (instead of select * from, use select #result(...)[,  
#result(...)] from).
   2) use #bind or #bindEqual directives for parameter binding.  This  
ultimately results in Cayenne using prepared statements and plugging  
in parameters supplied from a map at runtime, so that the parameter is  
properly escaped by the database driver when you execute the query.

For example:

SQLTemplate template = new SQLTemplate(Person.class,
   "SELECT #result(...) FROM people where country_id  
#bindEqual($countryId) and name #bindEqual($name)");

Map<String,Object> params = new HashMap<String,Object>();
params.put("countryId",country.getId());
params.put("name",name);
template.setParameters(params);
dataContext().performQuery(template);

See: http://cayenne.apache.org/doc20/scripting-sqltemplate.html for  
more information on #result, #bind, and #bindEqual
See: http://cayenne.apache.org/doc20/selectquery.html for more  
information on select queries.

Robert

On Feb 11, 2009, at 2/1110:45 PM , Mark Fraser wrote:

> hello,
>
> I am using Cayenne 2.0.4 in a standalone application with Derby  
> embedded.
>
> The following code breaks for obvious reasons when there is an  
> apostrophe ("'") in the person's name.
>
> ---------
>
> SQLTemplate template = new SQLTemplate(Person.class, "SELECT * FROM  
> people where country_id=" + String.valueOf(country.getId()) + " and  
> name='" + name + "'");
>
> List res = dataContext().performQuery(template);
> ---------
>
> What is the best (Cayenne specific or otherwise) approach to dealing  
> with this problem?
>
> Thanks
>


Mime
View raw message