You are viewing a plain text version of this content. The canonical link for it is here.
Posted to taglibs-user@tomcat.apache.org by "Carole E. Mah" <ca...@mama.stg.brown.edu> on 2002/06/11 20:12:48 UTC
escaping single quotes in sql query
DBtags has an 'escapeSql' tag, but JSTL:sql does not.
So, using JSTL, how does one escape single quotes?
Thank you,
-carole
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Carole E. Mah carolem@stg.brown.edu
Senior Programmer/Analyst
Brown University Scholarly Technology Group
phn 401-863-2669
fax 401-863-9313
http://www.stg.brown.edu/
personal: http://www.stg.brown.edu/~carolem/
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: escaping single quotes in sql query
Posted by Paul DuBois <pa...@snake.net>.
>Dear Paul,
>
>I don't understand your reply, or how your examples relate to single quote
>marks in any way.
>
>Sorry to be so dense!
>
>I am just trying to keep mySQL from freaking out when it sees a quotation
>mark -- all I need to do is replace every occurrence of ' with \', e.g.
>if I were inserting a query by hand on the mySQL command line, I would do
>this:
No, no, no. :-)
*You* don't want do any of that. If you put ? in your query string
where you want a data value to go, and use <sql:param> to specify
the data value that should be bound to the placedholder, JSTL and
the database driver will do all the work for you.
Support you have a form with fields named smirgleblorf and grbldulb
(for lack of better names) and you want to insert the values into
a table. The parameter values are available as $param['param_name'],
so you can do this:
<sql:update var="count" dataSource="${conn}">
INSERT INTO foo VALUES(?,?)
<sql:param value="${param['smirgleblorf']}" />
<sql:param value="${param['grbldulb']}" />
</sql:update>
If the parameter values have quotes in them, or if they don't, doesn't
matter. JSTL/JDBC will handle escaping of any that are present for you.
The nice thing about this is that you don't have to care whether or
not quotes (or backslashes, or other ugly special characters) are
present in the parameter values. They simply become irrelevant.
Where you'll get into trouble is by trying to figure out how to insert
the data values into the query string yourself (as you've apparently
been trying to do). But that's really re-inventing the wheel.
> INSERT INTO foo VALUES("bar", "blort, "Paul\'s example");
>But since I don't know if, when or how many single quotes a user might try
>to put into the input form, I need something less manual to deal with it.
>
>I suppose there is probably a regexp tag library of that would do this?
>i.e. the perl equivalent would be s/'/\'/g
>
>...
>
>Ok, yes, I've just looked at the regexp taglib documentation, and it does
>look as if that is my answer.
It's useful for solving certain problems.
But this ain't one of them. :-)
>
>Sorry to have bother you!
>
>-carole
>
>On Tue, 11 Jun 2002, Paul DuBois wrote:
>> At 14:12 -0400 6/11/02, Carole E. Mah wrote:
>
>> >DBtags has an 'escapeSql' tag, but JSTL:sql does not.
>>
>> Use <sql:param> in your <sql:query> or <sql:update> tag body,
>> and use ? as a placeholder character in the query string.
>>
>> <%-- placeholder value in <sql:param> body --%>
>> <sql:update var="count" dataSource="${conn}">
>> DELETE FROM tbl_name WHERE id > ?
>> <sql:param>100</sql:param>
>> </sql:update>
>>
>> <%-- placeholder value in <sql:param> value attribute --%>
>> <sql:query var="rs" dataSource="${conn}">
>> SELECT id, name FROM tbl_name WHERE cats = ? AND color = ?
>> <sql:param value="1" />
>> <sql:param value="green" />
>> </sql:query>
>>
>> >
>> >So, using JSTL, how does one escape single quotes?
>> >
> > >Thank you,
> > >-carole
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: escaping single quotes in sql query
Posted by Morris Hirsch <mo...@mama.stg.brown.edu>.
If you use the ? in your SQL statement and put the evil string
that has quotes between <param>It's OK here</param> tags
you should be alright. It is not really SQL that minds,
just need a way to be clear about what you are passing to SQL.
Hope this helps (and is correct)
Morris
On Tue, 11 Jun 2002, Carole E. Mah wrote:
>
> Dear Paul,
>
> I don't understand your reply, or how your examples relate to single quote
> marks in any way.
>
> Sorry to be so dense!
>
> I am just trying to keep mySQL from freaking out when it sees a quotation
> mark -- all I need to do is replace every occurrence of ' with \', e.g.
> if I were inserting a query by hand on the mySQL command line, I would do
> this:
> INSERT INTO foo VALUES("bar", "blort, "Paul\'s example");
> But since I don't know if, when or how many single quotes a user might try
> to put into the input form, I need something less manual to deal with it.
>
> I suppose there is probably a regexp tag library of that would do this?
> i.e. the perl equivalent would be s/'/\'/g
>
> ...
>
> Ok, yes, I've just looked at the regexp taglib documentation, and it does
> look as if that is my answer.
>
> Sorry to have bother you!
>
> -carole
>
> On Tue, 11 Jun 2002, Paul DuBois wrote:
> > At 14:12 -0400 6/11/02, Carole E. Mah wrote:
>
> > >DBtags has an 'escapeSql' tag, but JSTL:sql does not.
> >
> > Use <sql:param> in your <sql:query> or <sql:update> tag body,
> > and use ? as a placeholder character in the query string.
> >
> > <%-- placeholder value in <sql:param> body --%>
> > <sql:update var="count" dataSource="${conn}">
> > DELETE FROM tbl_name WHERE id > ?
> > <sql:param>100</sql:param>
> > </sql:update>
> >
> > <%-- placeholder value in <sql:param> value attribute --%>
> > <sql:query var="rs" dataSource="${conn}">
> > SELECT id, name FROM tbl_name WHERE cats = ? AND color = ?
> > <sql:param value="1" />
> > <sql:param value="green" />
> > </sql:query>
> >
> > >
> > >So, using JSTL, how does one escape single quotes?
> > >
> > >Thank you,
> > >-carole
> > >
> > >- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> > >Carole E. Mah carolem@stg.brown.edu
> > > Senior Programmer/Analyst
> > > Brown University Scholarly Technology Group
> > > phn 401-863-2669
> > > fax 401-863-9313
> > > http://www.stg.brown.edu/
> > > personal: http://www.stg.brown.edu/~carolem/
> >
> >
> > --
> > To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> > For additional commands, e-mail: <ma...@jakarta.apache.org>
> >
> >
>
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> Carole E. Mah carolem@stg.brown.edu
> Senior Programmer/Analyst
> Brown University Scholarly Technology Group
> phn 401-863-2669
> fax 401-863-9313
> http://www.stg.brown.edu/
> personal: http://www.stg.brown.edu/~carolem/
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: escaping single quotes in sql query
Posted by "Carole E. Mah" <ca...@mama.stg.brown.edu>.
Dear Paul,
I don't understand your reply, or how your examples relate to single quote
marks in any way.
Sorry to be so dense!
I am just trying to keep mySQL from freaking out when it sees a quotation
mark -- all I need to do is replace every occurrence of ' with \', e.g.
if I were inserting a query by hand on the mySQL command line, I would do
this:
INSERT INTO foo VALUES("bar", "blort, "Paul\'s example");
But since I don't know if, when or how many single quotes a user might try
to put into the input form, I need something less manual to deal with it.
I suppose there is probably a regexp tag library of that would do this?
i.e. the perl equivalent would be s/'/\'/g
...
Ok, yes, I've just looked at the regexp taglib documentation, and it does
look as if that is my answer.
Sorry to have bother you!
-carole
On Tue, 11 Jun 2002, Paul DuBois wrote:
> At 14:12 -0400 6/11/02, Carole E. Mah wrote:
> >DBtags has an 'escapeSql' tag, but JSTL:sql does not.
>
> Use <sql:param> in your <sql:query> or <sql:update> tag body,
> and use ? as a placeholder character in the query string.
>
> <%-- placeholder value in <sql:param> body --%>
> <sql:update var="count" dataSource="${conn}">
> DELETE FROM tbl_name WHERE id > ?
> <sql:param>100</sql:param>
> </sql:update>
>
> <%-- placeholder value in <sql:param> value attribute --%>
> <sql:query var="rs" dataSource="${conn}">
> SELECT id, name FROM tbl_name WHERE cats = ? AND color = ?
> <sql:param value="1" />
> <sql:param value="green" />
> </sql:query>
>
> >
> >So, using JSTL, how does one escape single quotes?
> >
> >Thank you,
> >-carole
> >
> >- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> >Carole E. Mah carolem@stg.brown.edu
> > Senior Programmer/Analyst
> > Brown University Scholarly Technology Group
> > phn 401-863-2669
> > fax 401-863-9313
> > http://www.stg.brown.edu/
> > personal: http://www.stg.brown.edu/~carolem/
>
>
> --
> To unsubscribe, e-mail: <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Carole E. Mah carolem@stg.brown.edu
Senior Programmer/Analyst
Brown University Scholarly Technology Group
phn 401-863-2669
fax 401-863-9313
http://www.stg.brown.edu/
personal: http://www.stg.brown.edu/~carolem/
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: escaping single quotes in sql query
Posted by Paul DuBois <pa...@snake.net>.
At 14:12 -0400 6/11/02, Carole E. Mah wrote:
>DBtags has an 'escapeSql' tag, but JSTL:sql does not.
Use <sql:param> in your <sql:query> or <sql:update> tag body,
and use ? as a placeholder character in the query string.
<%-- placeholder value in <sql:param> body --%>
<sql:update var="count" dataSource="${conn}">
DELETE FROM tbl_name WHERE id > ?
<sql:param>100</sql:param>
</sql:update>
<%-- placeholder value in <sql:param> value attribute --%>
<sql:query var="rs" dataSource="${conn}">
SELECT id, name FROM tbl_name WHERE cats = ? AND color = ?
<sql:param value="1" />
<sql:param value="green" />
</sql:query>
>
>So, using JSTL, how does one escape single quotes?
>
>Thank you,
>-carole
>
>- - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>Carole E. Mah carolem@stg.brown.edu
> Senior Programmer/Analyst
> Brown University Scholarly Technology Group
> phn 401-863-2669
> fax 401-863-9313
> http://www.stg.brown.edu/
> personal: http://www.stg.brown.edu/~carolem/
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>