Re: single quotation confusion

[David Johnston]

R10 wrote
> i am connecting a postgresql database to a netbeans form , and when i try
> to execute a query which includes a string entered through a Jtextfield
> that contain the special charachter " ' " it reads it as a single
> quotation
> for example if a String txt = "it's cold" and to be inserted into a sql
> query "insert into test values ('"+txt+"')";
> it gives me
> syntax error at or near "s"
>
> any ideas how to solve that problem ??

In short you do not build queries by directly concatenating user-supplied
data. You use a PREPARED STATEMENT with placeholders and then use the
setXXX methods to map the data.

SQL: INSERT INTO test VALUES (?)

Others may be able to provide more concrete suggestions but otherwise find
some books/articles on database programming in Java. This is not a
PostgreSQL specific topic/solution. I've known this for so long now I
really cannot point you to any educational materials.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/single-quotation-confusion-tp5751065p5751093.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[Kevin Grittner]

David Johnston <pol...@yahoo.com> wrote:
> R10 wrote

>> for example if a String txt = "it's cold" and to be inserted
>> into a sql query "insert into test values ('"+txt+"')";
>> it gives me
>> syntax error at or near "s"
>>
>> any ideas how to solve that problem ??
>
> In short you do not build queries by directly concatenating
> user-supplied data.� You use a PREPARED STATEMENT with
> placeholders and then use the setXXX methods to map the data.
>
> SQL: INSERT INTO test VALUES (?)

Yeah, David's suggestion is the way to go.� Trying to handle this
in an ad hoc fashion opens you to a class of attack known as "SQL
injection".� When concatenating without proper handling of quotes
(which drivers will do for you) you should consider yourself
*lucky* if the worst you have seen is errors.

http://xkcd.com/327/

The above link also illustrates why you shouldn't run the
application under a user ID which has permission to execute DML.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[R10]

i am connecting a postgresql database to a netbeans form , and when i try to
execute a query which includes a string entered through a Jtextfield that
contain the special charachter " ' " it reads it as a single quotation

for example if a String txt = "it's cold" and to be inserted into a sql
query "insert into test values ('"+txt+"')";
it gives me
syntax error at or near "s"

any ideas how to solve that problem ??

--
View this message in context: http://postgresql.1045698.n5.nabble.com/single-quotation-confusion-tp5751065.html

Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.