From monroy@mindspring.com Mon Nov  25 12:58:08 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D133D47592C
	for <pgsql-sql@postgresql.org>; Mon, 25 Nov 2002 07:58:08 -0500 (EST)
Received: from news.hub.org (news.hub.org [64.49.215.80])
	by postgresql.org (Postfix) with ESMTP id 8B3484758FE
	for <pgsql-sql@postgresql.org>; Mon, 25 Nov 2002 07:58:07 -0500 (EST)
Received: by news.hub.org (Postfix, from userid 8)
	id 5D48B43A5A1; Mon, 25 Nov 2002 07:58:06 -0500 (EST)
From: monroy@mindspring.com (javaholic)
X-Newsgroups: comp.databases.postgresql.sql
Subject: How does postgres handle non literal string values
Date: 25 Nov 2002 04:57:31 -0800
Organization: http://groups.google.com/
Lines: 128
Message-ID: <8adde2e2.0211250457.3f0d1c7c@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: groups-abuse@google.com
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/386
X-Sequence-Number: 10528

Hi All,

I have some jsp code that should insert a user name and password into
a table called login.

Instead of inserting the values given by the client, it insert the
literal string 'username' and 'password. The problem is somewhere in
the INSERT statement.

Here is the code:

<%@page contentType="text/html"%>
<%@page import="java.io.*" %>
<%@page import="java.sql.*" %>
<%@page import="java.util.*" %>

<html>
<head><title>JSP login</title></head>
<body>

<%-- <jsp:useBean id="beanInstanceName" scope="session"
class="package.class" /> --%>
<%-- <jsp:getProperty name="beanInstanceName"  property="propertyName"
/> --%>

<%
String username = request.getParameter("username");
String password = request.getParameter("password");
String confirmpw = request.getParameter("password2");
String dbName = "storedb";


Connection conn = null;
Statement stmt = null;


String usr = "postgres";
String passwd = "Wimdk12";

if (username != null)
    username = username.trim();
if (password != null)
    password = password.trim();
if(confirmpw != null)
    confirmpw = confirmpw.trim();
if (username != null &&
        username.length() > 0) {
        if (password != null &&
            password.length() > 0) {
            if (confirmpw != null &&
                confirmpw.length() > 0) {
                if (password.equals(confirmpw)) {
%>
                        <h1> Loading the driver </h1>
<%
                        String url = "jdbc:postgresql:" + dbName;

                        // Load the driver
                        Class.forName("org.postgresql.Driver");
                        // Connect to database
                        conn = DriverManager.getConnection(url, usr,
passwd);
                        stmt = conn.createStatement();
%>

                       <h1> Connecting to the data base </h1>
<%
                        String insertString =
                        "INSERT INTO  \"login\" (\'user\', \'password\')
VALUES ('username', 'password')";
%>

                        <h1> Updating table </h1>
<%
                            stmt.executeUpdate(insertString);

%>
                        <h1> Checking result </h1>
<%
                        ResultSet rset = stmt.executeQuery("SELECT *
FROM login");


                        while (rset.next()) {
                            System.out.println(
                                rset.getString("user") + ":" +
                                rset.getString("password"));
                        }
%>
                        <h1> Closing connection <h1>
<%
                        rset.close();
                        stmt.close();
                        conn.close();

%>
<h1>Congratulations <%= username %>! your account has been created
</h1>

<%
                } else { %>
                      <h1>Sorry! Account not created.  passwords do
not match </h1>
<%
                }
              } else {  %>                      <h1>Sorry! Account not
created.  passwords do not match </h1>
<%
                }
              } else {  %>
                     <h1>Sorry! Account not created. Please enter a
confirmation password </h1>
<%
                }
              } else { %>
                     <h1>Sorry! Account not created. Please enter a
password </h1>
<%
                }
              } else { %>
                     <h1>Sorry! Account not created. Please enter a
username </h1>
<%
                }  %>
</body>
</html>

Any help on this is greatly appreciated.



From SHenshall@westcountry-design-print.co.uk Tue Nov  26 15:14:46 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D2DFA47655B
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 10:14:46 -0500 (EST)
Received: from wcpexchange.westcountrypublications.co.uk (unknown
	[195.171.163.155])
	by postgresql.org (Postfix) with ESMTP id C875047641E
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 10:14:44 -0500 (EST)
Received: by WCPEXCHANGE with Internet Mail Service (5.5.2653.19)
	id <XLABWBP8>; Tue, 26 Nov 2002 15:12:03 -0000
Message-ID: <E382B5D8EDE1D6118DBE0008C759BCD6116AE1@WCPEXCHANGE>
From: "Henshall,
	Stuart - Design & Print" <SHenshall@westcountry-design-print.co.uk>
To: "'monroy@mindspring.com'" <monroy@mindspring.com>,
 pgsql-sql@postgresql.org
Subject: Re: How does postgres handle non literal string values
Date: Tue, 26 Nov 2002 15:12:02 -0000
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2653.19)
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C2955E.2CA042C0"
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/389
X-Sequence-Number: 10531

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C2955E.2CA042C0
Content-Type: text/plain;
	charset="iso-8859-1"

I'm guessing it would have to be more like:
<%
                        String insertString =
                        "INSERT INTO  \"login\" (\'user\', \'password\')
VALUES ('"+username+"', '"+password+"')";
%>
to actually concatonate a string including the username & password
variables, however I've not really used Java much so you might want to ask
on the pgsql-jdbc@postgresql.org list.
hth,
- Stuart

monroy@mindspring.com wrote:
> Hi All,
> 
> I have some jsp code that should insert a user name and password into
> a table called login. 
> 
> Instead of inserting the values given by the client, it insert the
> literal string 'username' and 'password. The problem is somewhere in
> the INSERT statement. 
> 
> Here is the code:
> 
> <%@page contentType="text/html"%>
> <%@page import="java.io.*" %>
> <%@page import="java.sql.*" %>
> <%@page import="java.util.*" %>
> 
> <html>
> <head><title>JSP login</title></head>
> <body>
> 
> <%-- <jsp:useBean id="beanInstanceName" scope="session"
> class="package.class" /> --%> <%-- <jsp:getProperty
> name="beanInstanceName"  property="propertyName" /> --%> 
> 
> <%
> String username = request.getParameter("username");
> String password = request.getParameter("password");
> String confirmpw = request.getParameter("password2"); String dbName =
> "storedb"; 
> 
> 
> Connection conn = null;
> Statement stmt = null;
> 
> 
> String usr = "postgres";
> String passwd = "Wimdk12";
> 
> if (username != null)
>     username = username.trim();
> if (password != null)
>     password = password.trim();
> if(confirmpw != null)
>     confirmpw = confirmpw.trim();
> if (username != null &&
>         username.length() > 0) {
>         if (password != null &&
>             password.length() > 0) {
>             if (confirmpw != null &&
>                 confirmpw.length() > 0) {
>                 if (password.equals(confirmpw)) {
> %>
>                         <h1> Loading the driver </h1> <%
>                         String url = "jdbc:postgresql:" + dbName;
> 
>                         // Load the driver
>                         Class.forName("org.postgresql.Driver");
>                         // Connect to database
>                         conn = DriverManager.getConnection(url, usr,
>                         passwd); stmt = conn.createStatement(); %>
> 
>                        <h1> Connecting to the data base </h1> <%
>                         String insertString =
>                         "INSERT INTO  \"login\" (\'user\',
> \'password\') VALUES ('username', 'password')";
> %>
> 
>                         <h1> Updating table </h1>
> <%
>                             stmt.executeUpdate(insertString);
> 
> %>
>                         <h1> Checking result </h1>
> <%
>                         ResultSet rset = stmt.executeQuery("SELECT *
> FROM login"); 
> 
> 
>                         while (rset.next()) {
>                             System.out.println(
>                                 rset.getString("user") + ":" +
>                                 rset.getString("password"));         
> } %>
>                         <h1> Closing connection <h1> <%
>                         rset.close();
>                         stmt.close();
>                         conn.close();
> 
> %>
> <h1>Congratulations <%= username %>! your account has been created
> </h1> 
> 
> <%
>                 } else { %>
>                       <h1>Sorry! Account not created.  passwords do
> not match </h1> <%
>                 }
>               } else {  %>                      <h1>Sorry! Account not
> created.  passwords do not match </h1>
> <%
>                 }
>               } else {  %>
>                      <h1>Sorry! Account not created. Please enter a
> confirmation password </h1> <%
>                 }
>               } else { %>
>                      <h1>Sorry! Account not created. Please enter a
> password </h1> <%
>                 }
>               } else { %>
>                      <h1>Sorry! Account not created. Please enter a
> username </h1> <%
>                 }  %>
> </body>
> </html>
> 


------_=_NextPart_001_01C2955E.2CA042C0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Diso-8859-=
1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version 5.5.2653.12">
<TITLE>RE: [SQL] How does postgres handle non literal string values</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>I'm guessing it would have to be more like:</FONT>
<BR><FONT SIZE=3D2>&lt;%</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp; String insertString =3D</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp; &quot;INSERT INTO&nbsp; \&quot;login\&quot; (\'user\', \'password\'=
)</FONT>
<BR><FONT SIZE=3D2>VALUES ('&quot;+username+&quot;', '&quot;+password+&quot=
;')&quot;;</FONT>
<BR><FONT SIZE=3D2>%&gt;</FONT>
<BR><FONT SIZE=3D2>to actually concatonate a string including the username =
&amp; password variables, however I've not really used Java much so you mig=
ht want to ask on the pgsql-jdbc@postgresql.org list.</FONT></P>

<P><FONT SIZE=3D2>hth,</FONT>
<BR><FONT SIZE=3D2>- Stuart</FONT>
</P>

<P><FONT SIZE=3D2>monroy@mindspring.com wrote:</FONT>
<BR><FONT SIZE=3D2>&gt; Hi All,</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; I have some jsp code that should insert a user name=
 and password into</FONT>
<BR><FONT SIZE=3D2>&gt; a table called login. </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Instead of inserting the values given by the client=
, it insert the</FONT>
<BR><FONT SIZE=3D2>&gt; literal string 'username' and 'password. The proble=
m is somewhere in</FONT>
<BR><FONT SIZE=3D2>&gt; the INSERT statement. </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Here is the code:</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%@page contentType=3D&quot;text/html&quot;%&gt;=
</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%@page import=3D&quot;java.io.*&quot; %&gt;</FO=
NT>
<BR><FONT SIZE=3D2>&gt; &lt;%@page import=3D&quot;java.sql.*&quot; %&gt;</F=
ONT>
<BR><FONT SIZE=3D2>&gt; &lt;%@page import=3D&quot;java.util.*&quot; %&gt;</=
FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; &lt;html&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;head&gt;&lt;title&gt;JSP login&lt;/title&gt;&lt=
;/head&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;body&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%-- &lt;jsp:useBean id=3D&quot;beanInstanceName=
&quot; scope=3D&quot;session&quot;</FONT>
<BR><FONT SIZE=3D2>&gt; class=3D&quot;package.class&quot; /&gt; --%&gt; &lt=
;%-- &lt;jsp:getProperty</FONT>
<BR><FONT SIZE=3D2>&gt; name=3D&quot;beanInstanceName&quot;&nbsp; property=
=3D&quot;propertyName&quot; /&gt; --%&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt; String username =3D request.getParameter(&quot;user=
name&quot;);</FONT>
<BR><FONT SIZE=3D2>&gt; String password =3D request.getParameter(&quot;pass=
word&quot;);</FONT>
<BR><FONT SIZE=3D2>&gt; String confirmpw =3D request.getParameter(&quot;pas=
sword2&quot;); String dbName =3D</FONT>
<BR><FONT SIZE=3D2>&gt; &quot;storedb&quot;; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; Connection conn =3D null;</FONT>
<BR><FONT SIZE=3D2>&gt; Statement stmt =3D null;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; String usr =3D &quot;postgres&quot;;</FONT>
<BR><FONT SIZE=3D2>&gt; String passwd =3D &quot;Wimdk12&quot;;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; if (username !=3D null)</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp; username =3D username.trim(=
);</FONT>
<BR><FONT SIZE=3D2>&gt; if (password !=3D null)</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp; password =3D password.trim(=
);</FONT>
<BR><FONT SIZE=3D2>&gt; if(confirmpw !=3D null)</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp; confirmpw =3D confirmpw.tri=
m();</FONT>
<BR><FONT SIZE=3D2>&gt; if (username !=3D null &amp;&amp;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; use=
rname.length() &gt; 0) {</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if =
(password !=3D null &amp;&amp;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp; password.length() &gt; 0) {</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp; if (confirmpw !=3D null &amp;&amp;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; confirmpw.length() &gt; 0) {</=
FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (password.equals(confirmpw)=
) {</FONT>
<BR><FONT SIZE=3D2>&gt; %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; &lt;h1&gt; Loading the driver &lt;/h1&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; String url =3D &quot;jdbc:postgresql:&quot; + dbName;</FO=
NT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; // Load the driver</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; Class.forName(&quot;org.postgresql.Driver&quot;);</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; // Connect to database</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; conn =3D DriverManager.getConnection(url, usr,</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; passwd); stmt =3D conn.createStatement(); %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp; &lt;h1&gt; Connecting to the data base &lt;/h1&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; String insertString =3D</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; &quot;INSERT INTO&nbsp; \&quot;login\&quot; (\'user\',</F=
ONT>
<BR><FONT SIZE=3D2>&gt; \'password\') VALUES ('username', 'password')&quot;=
;</FONT>
<BR><FONT SIZE=3D2>&gt; %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; &lt;h1&gt; Updating table &lt;/h1&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; stmt.executeUpdate(insertString);=
</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; &lt;h1&gt; Checking result &lt;/h1&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; ResultSet rset =3D stmt.executeQuery(&quot;SELECT *</FONT>
<BR><FONT SIZE=3D2>&gt; FROM login&quot;); </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; while (rset.next()) {</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.out.println(</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rset.getS=
tring(&quot;user&quot;) + &quot;:&quot; +</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rset.getS=
tring(&quot;password&quot;));&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p; </FONT>
<BR><FONT SIZE=3D2>&gt; } %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; &lt;h1&gt; Closing connection &lt;h1&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; rset.close();</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; stmt.close();</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp; conn.close();</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;h1&gt;Congratulations &lt;%=3D username %&gt;! =
your account has been created</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;/h1&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else { %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; &lt;h1&gt;Sorry! Account not created.&nbsp; passwords do</FONT>
<BR><FONT SIZE=3D2>&gt; not match &lt;/h1&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {&nbsp; %&gt;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;h1&gt;Sorry! Account not</FONT>
<BR><FONT SIZE=3D2>&gt; created.&nbsp; passwords do not match &lt;/h1&gt;</=
FONT>
<BR><FONT SIZE=3D2>&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {&nbsp; %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&lt;h1&gt;Sorry! Account not created. Please enter a</FONT>
<BR><FONT SIZE=3D2>&gt; confirmation password &lt;/h1&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else { %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&lt;h1&gt;Sorry! Account not created. Please enter a</FONT>
<BR><FONT SIZE=3D2>&gt; password &lt;/h1&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else { %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
&lt;h1&gt;Sorry! Account not created. Please enter a</FONT>
<BR><FONT SIZE=3D2>&gt; username &lt;/h1&gt; &lt;%</FONT>
<BR><FONT SIZE=3D2>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp; %&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;/body&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; &lt;/html&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; </FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C2955E.2CA042C0--



From dev@archonet.com Tue Nov  26 15:45:10 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 916A247649F
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 10:45:10 -0500 (EST)
Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net
	[194.217.242.80])
	by postgresql.org (Postfix) with ESMTP id 0C44447641E
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 10:45:09 -0500 (EST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2)
	id 18Ghtb-0003ze-0U; Tue, 26 Nov 2002 15:45:08 +0000
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id B5AAC17507; Tue, 26 Nov 2002 15:35:05 +0000 (GMT)
Received: from client.archonet.com (client.archonet.com [192.168.1.16])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 25E9D17506; Tue, 26 Nov 2002 15:35:05 +0000 (GMT)
Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: monroy@mindspring.com (javaholic), pgsql-sql@postgresql.org
Subject: Re: How does postgres handle non literal string values
Date: Tue, 26 Nov 2002 15:35:02 +0000
User-Agent: KMail/1.4.3
References: <8adde2e2.0211250457.3f0d1c7c@posting.google.com>
In-Reply-To: <8adde2e2.0211250457.3f0d1c7c@posting.google.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211261535.02419.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/394
X-Sequence-Number: 10536

On Monday 25 Nov 2002 12:57 pm, javaholic wrote:
> Hi All,
>
> I have some jsp code that should insert a user name and password into
> a table called login.
>
> Instead of inserting the values given by the client, it insert the
> literal string 'username' and 'password. The problem is somewhere in
> the INSERT statement.

>                         String insertString =3D
>                         "INSERT INTO  \"login\" (\'user\', \'password\')
> VALUES ('username', 'password')";

You're creating an insertString with the literal words "username" and=20
"password" in them. This is a java issue, not a PG issue. You'll want=20
something like (sorry if syntax is wrong, but I don't do Java):

String insertString =3D "Insert INTO ... VALUES ('" + sql_escaped(username)=
 +=20
"')..."

You *will* want to escape the username and password otherwise I'll be able =
to=20
come along and insert any values I like into your database. I can't believe=
=20
the JDBC classes don't provide=20

1. Some way to escape value strings
2. Some form of placeholders to deal with this

--=20
  Richard Huxton



From chw@clearmetrix.com Tue Nov  26 16:05:37 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 7E7B7475CE5
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 11:05:37 -0500 (EST)
Received: from clearmetrix.com (unknown [209.92.142.67])
	by postgresql.org (Postfix) with ESMTP id C34A2475BEC
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 11:05:36 -0500 (EST)
Received: from clearmetrix.com (chw.muvpn.clearmetrix.com [172.16.1.3])
	by clearmetrix.com (8.11.6/linuxconf) with ESMTP id gAQG5bQ09606;
	Tue, 26 Nov 2002 11:05:37 -0500
Message-ID: <3DE39BC7.8000603@clearmetrix.com>
Date: Tue, 26 Nov 2002 11:05:27 -0500
From: "Charles H. Woloszynski" <chw@clearmetrix.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.1) Gecko/20020826
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Richard Huxton <dev@archonet.com>
Cc: javaholic <monroy@mindspring.com>, pgsql-sql@postgresql.org
Subject: Re: How does postgres handle non literal string values
References: <8adde2e2.0211250457.3f0d1c7c@posting.google.com>
	<200211261535.02419.dev@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/400
X-Sequence-Number: 10542

Actually, we use JDBC Prepared Statements for this type of work.  You 
put a query with '?' in as placeholders and then add in the values and 
the library takes care of the encoding issues.  This avoids the double 
encoding of (encode X as String, decode string and encode as SQL X on 
the line).  There was a good article about a framework that did this in  
JavaReport about a 18 months ago.  

We have gleaned some ideas from that article to create a framework 
around using PreparedStatements as the primary interface to the 
database.  I'd suggest looking at them.  They really make your code much 
more robust.

Charlie


>"')..."
>
>You *will* want to escape the username and password otherwise I'll be able to 
>come along and insert any values I like into your database. I can't believe 
>the JDBC classes don't provide 
>
>1. Some way to escape value strings
>2. Some form of placeholders to deal with this
>
>  
>

-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com







From rob@netpaver.com.au Tue Nov  26 17:19:18 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 63818475B07
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 12:19:18 -0500 (EST)
Received: from mail.iinet.net.au (mail-03.iinet.net.au [203.59.3.35])
	by postgresql.org (Postfix) with SMTP id 9113D475E4A
	for <pgsql-sql@postgresql.org>; Tue, 26 Nov 2002 12:19:13 -0500 (EST)
Received: (qmail 20081 invoked from network); 26 Nov 2002 17:19:16 -0000
Received: from unknown (HELO NETPAVER-PBGNMG) (203.59.155.212)
	by mail.iinet.net.au with SMTP; 26 Nov 2002 17:19:16 -0000
From: "Rob Hills" <rob@netpaver.com.au>
Organization: NetPaver Pty Ltd
To: pgsql-sql@postgresql.org
Date: Wed, 27 Nov 2002 01:18:02 +0800
MIME-Version: 1.0
Subject: Re: How does postgres handle non literal string values
Reply-To: rob.hills@netpaver.com.au
Message-ID: <3DE41D4A.9268.9F992F@localhost>
In-reply-to: <8adde2e2.0211250457.3f0d1c7c@posting.google.com>
X-mailer: Pegasus Mail for Windows (v4.02)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200211/403
X-Sequence-Number: 10545

Hi,

On 25 Nov 2002 at 4:57, javaholic wrote:

Your problem is really a JSP one rather than a postgres problem, and probably doesn't 
really belong on this list.  That said, I know much more java/jsp than I do postgres, so I'll 
try and help.

> I have some jsp code that should insert a user name and password into
> a table called login.
> 
> Instead of inserting the values given by the client, it insert the
> literal string 'username' and 'password. The problem is somewhere in
> the INSERT statement.

Yup, your INSERT statement is doing exactly what you've asked it to do, inserting the 
literal strings 'username' and 'password' into the table.

> <%
>                         String insertString =
>                         "INSERT INTO  \"login\" (\'user\', \'password\')
> VALUES ('username', 'password')";
> %>

To do it correctly using JSP, try the following:

<% 
	String insertString = "INSERT INTO  \"login\" (\'user\', \'password\') 	
		VALUES ('" + username + "', '" + password + "')"; 
%>  

However, you would probably be better off using a PreparedStatement object rather 
than a Statement for various reasons, but especially to avoid trying to get the single- 
and double-quotes right in the above statement.

HTH,


Rob Hills
MBBS, Grad Dip Com Stud, MACS
Senior Consultant
Netpaver Web Solutions
Tel:	(0412) 904 357
Fax:	(08) 9485 2555
Email:	rob@netpaver.com.au




From vernonw@gatewaytech.com Wed Dec  4 05:26:12 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id F2910475B8C
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 00:26:12 -0500 (EST)
Received: from pd3mo3so.prod.shaw.ca (shawidc-mo1.cg.shawcable.net
	[24.71.223.10]) by postgresql.org (Postfix) with ESMTP id 8947B475EBC
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 00:26:11 -0500 (EST)
Received: from pd6mr1so.prod.shaw.ca
	(pd6mr1so-qfe3.prod.shaw.ca [10.0.141.216]) by l-daemon
	(iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002))
	with ESMTP id <0H6K0095CXLI6G@l-daemon> for pgsql-sql@postgresql.org;
	Tue, 03 Dec 2002 22:22:30 -0700 (MST)
Received: from pn2ml9so.prod.shaw.ca (pn2ml9so-qfe0.prod.shaw.ca [10.0.121.7])
	by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12
	2002))
	with ESMTP id <0H6K003R1XLIHK@l-daemon> for pgsql-sql@postgresql.org;
	Tue, 03 Dec 2002 22:22:30 -0700 (MST)
Received: from kimiko (h24-78-132-76.vc.shawcable.net [24.78.132.76])
	by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12
	2002))
	with SMTP id <0H6K006H0XLHD4@l-daemon> for pgsql-sql@postgresql.org;
	Tue, 03 Dec 2002 22:22:30 -0700 (MST)
Date: Tue, 03 Dec 2002 21:22:37 -0800
From: Vernon Wu <vernonw@gatewaytech.com>
Subject: Re: How does postgres handle non literal string values
In-reply-to: <3DE39BC7.8000603@clearmetrix.com>
To: pgsql-sql@postgresql.org
Reply-To: vernonw@gatewaytech.com
Message-id: <2FXV72GHB762VC01VURWMGPMNMD8.3ded911d@kimiko>
MIME-version: 1.0
X-Mailer: Opera 6.05 build 1140
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
X-Priority: 3 (Normal)
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/47
X-Sequence-Number: 10635

In general, it isn't a good idea to have SQL statements in JSP files. A good practise is using Mode 2. The Struts is a 
popular Mode 2 framework. If your application is very small and it won't grow into a big one, you can get around using 
Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism.

11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <chw@clearmetrix.com> wrote:

>Actually, we use JDBC Prepared Statements for this type of work.  You 
>put a query with '?' in as placeholders and then add in the values and 
>the library takes care of the encoding issues.  This avoids the double 
>encoding of (encode X as String, decode string and encode as SQL X on 
>the line).  There was a good article about a framework that did this in  
>JavaReport about a 18 months ago.  
>
>We have gleaned some ideas from that article to create a framework 
>around using PreparedStatements as the primary interface to the 
>database.  I'd suggest looking at them.  They really make your code much 
>more robust.
>
>Charlie
>
>
>>"')..."
>>
>>You *will* want to escape the username and password otherwise I'll be able to 
>>come along and insert any values I like into your database. I can't believe 
>>the JDBC classes don't provide 
>>
>>1. Some way to escape value strings
>>2. Some form of placeholders to deal with this
>>
>>  
>>
>
>-- 
>
>
>Charles H. Woloszynski
>
>ClearMetrix, Inc.
>115 Research Drive
>Bethlehem, PA 18015
>
>tel: 610-419-2210 x400
>fax: 240-371-3256
>web: www.clearmetrix.com
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>






From chw@clearmetrix.com Wed Dec  4 12:58:30 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0BB09475AE6
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 07:58:30 -0500 (EST)
Received: from clearmetrix.com (unknown [209.92.142.67])
	by postgresql.org (Postfix) with ESMTP id 587054759AF
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 07:58:29 -0500 (EST)
Received: from clearmetrix.com (chw.muvpn.clearmetrix.com [172.16.1.3])
	by clearmetrix.com (8.11.6/linuxconf) with ESMTP id gB4CwUW25906;
	Wed, 4 Dec 2002 07:58:30 -0500
Message-ID: <3DEDFC62.6060703@clearmetrix.com>
Date: Wed, 04 Dec 2002 08:00:18 -0500
From: "Charles H. Woloszynski" <chw@clearmetrix.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.1) Gecko/20020826
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: vernonw@gatewaytech.com
Cc: pgsql-sql@postgresql.org
Subject: Re: How does postgres handle non literal string values
References: <2FXV72GHB762VC01VURWMGPMNMD8.3ded911d@kimiko>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/50
X-Sequence-Number: 10638

Vernon:

Agreed.  We use Struts (as our MVC framework), and then a data access 
layer (we call persistables) that uses the PreparedStatements. Our JSPs 
only get data to render *after* the business logic has decided that all 
logic has been performed successfully.

The end-result is easily compartmentalized code (lots of code factoring) 
that makes for very robust applications.  We are working on moving this 
framework to PostgreSQL (from Oracle) and we expect to have to touch the 
SQL statements (which are each in their own class, again for re-use) and 
perhaps two or three other classes to deal with any JDBC driver issues. 
  When we make the transition successfully, I hope to be able to 
publicize the work and the value of PostgreSQL.

Charlie

Vernon Wu wrote:

>In general, it isn't a good idea to have SQL statements in JSP files. A good practise is using Mode 2. The Struts is a 
>popular Mode 2 framework. If your application is very small and it won't grow into a big one, you can get around using 
>Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism.
>
>11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <chw@clearmetrix.com> wrote:
>
>  
>
>>Actually, we use JDBC Prepared Statements for this type of work.  You 
>>put a query with '?' in as placeholders and then add in the values and 
>>the library takes care of the encoding issues.  This avoids the double 
>>encoding of (encode X as String, decode string and encode as SQL X on 
>>the line).  There was a good article about a framework that did this in  
>>JavaReport about a 18 months ago.  
>>
>>We have gleaned some ideas from that article to create a framework 
>>around using PreparedStatements as the primary interface to the 
>>database.  I'd suggest looking at them.  They really make your code much 
>>more robust.
>>
>>Charlie
>>
>>
>>    
>>
>>>"')..."
>>>
>>>You *will* want to escape the username and password otherwise I'll be able to 
>>>come along and insert any values I like into your database. I can't believe 
>>>the JDBC classes don't provide 
>>>
>>>1. Some way to escape value strings
>>>2. Some form of placeholders to deal with this
>>>
>>> 
>>>
>>>      
>>>
>>-- 
>>
>>
>>Charles H. Woloszynski
>>
>>ClearMetrix, Inc.
>>115 Research Drive
>>Bethlehem, PA 18015
>>
>>tel: 610-419-2210 x400
>>fax: 240-371-3256
>>web: www.clearmetrix.com
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>>    
>>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>  
>

-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com







From vernonw@gatewaytech.com Wed Dec  4 17:21:29 2002
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BE90447631A
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 12:21:29 -0500 (EST)
Received: from pd3mo3so.prod.shaw.ca (shawidc-mo1.cg.shawcable.net
	[24.71.223.10]) by postgresql.org (Postfix) with ESMTP id 7DDC347691A
	for <pgsql-sql@postgresql.org>; Wed,  4 Dec 2002 12:14:32 -0500 (EST)
Received: from pd2mr2so.prod.shaw.ca (pd2mr2so-ser.prod.shaw.ca
 [10.0.141.109])
	by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12
	2002))
	with ESMTP id <0H6L00186UFPBU@l-daemon> for pgsql-sql@postgresql.org;
	Wed, 04 Dec 2002 10:11:49 -0700 (MST)
Received: from pn2ml4so.prod.shaw.ca
	(pn2ml4so-qfe0.prod.shaw.ca [10.0.121.148]) by l-daemon
	(iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12 2002))
	with ESMTP id <0H6L00CDAUFPNL@l-daemon> for pgsql-sql@postgresql.org;
	Wed, 04 Dec 2002 10:11:49 -0700 (MST)
Received: from kimiko (h24-78-132-76.vc.shawcable.net [24.78.132.76])
	by l-daemon (iPlanet Messaging Server 5.1 HotFix 0.8 (built May 12
	2002))
	with SMTP id <0H6L00840UFOUP@l-daemon> for pgsql-sql@postgresql.org;
	Wed, 04 Dec 2002 10:11:49 -0700 (MST)
Date: Wed, 04 Dec 2002 09:11:55 -0800
From: Vernon Wu <vernonw@gatewaytech.com>
Subject: Re: How does postgres handle non literal string values
In-reply-to: <3DEDFC62.6060703@clearmetrix.com>
To: pgsql-sql@postgresql.org
Reply-To: vernonw@gatewaytech.com
Message-id: <GCKH616ZVUXTE0SQ1YLK5Z4YYVVSQLSM.3dee375b@kimiko>
MIME-version: 1.0
X-Mailer: Opera 6.05 build 1140
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
X-Priority: 3 (Normal)
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200212/52
X-Sequence-Number: 10640


When the MVC pattern is employed and EJB is absented, the DAO pattern shall be utilized. With this system design, the 
DB routines are isolated from the rest of system for the purpose of "robust" as you mentioned. In the future, you only 
need to write another set of DAO implemention if you decide to use another DB, say SAP DB for example. 

A DB connection pool also is desired, which shall take care of the JDBC driver look up.  

12/4/2002 5:00:18 AM, "Charles H. Woloszynski" <chw@clearmetrix.com> wrote:

>Vernon:
>
>Agreed.  We use Struts (as our MVC framework), and then a data access 
>layer (we call persistables) that uses the PreparedStatements. Our JSPs 
>only get data to render *after* the business logic has decided that all 
>logic has been performed successfully.
>
>The end-result is easily compartmentalized code (lots of code factoring) 
>that makes for very robust applications.  We are working on moving this 
>framework to PostgreSQL (from Oracle) and we expect to have to touch the 
>SQL statements (which are each in their own class, again for re-use) and 
>perhaps two or three other classes to deal with any JDBC driver issues. 
>  When we make the transition successfully, I hope to be able to 
>publicize the work and the value of PostgreSQL.
>
>Charlie
>
>Vernon Wu wrote:
>
>>In general, it isn't a good idea to have SQL statements in JSP files. A good practise is using Mode 2. The Struts is a 
>>popular Mode 2 framework. If your application is very small and it won't grow into a big one, you can get around 
using 
>>Mode 1. In the situation, the SQL tags of JSTL will be a recommeded mechanism.
>>
>>11/26/2002 8:05:27 AM, "Charles H. Woloszynski" <chw@clearmetrix.com> wrote:
>>
>>  
>>
>>>Actually, we use JDBC Prepared Statements for this type of work.  You 
>>>put a query with '?' in as placeholders and then add in the values and 
>>>the library takes care of the encoding issues.  This avoids the double 
>>>encoding of (encode X as String, decode string and encode as SQL X on 
>>>the line).  There was a good article about a framework that did this in  
>>>JavaReport about a 18 months ago.  
>>>
>>>We have gleaned some ideas from that article to create a framework 
>>>around using PreparedStatements as the primary interface to the 
>>>database.  I'd suggest looking at them.  They really make your code much 
>>>more robust.
>>>
>>>Charlie
>>>
>>>
>>>    
>>>
>>>>"')..."
>>>>
>>>>You *will* want to escape the username and password otherwise I'll be able to 
>>>>come along and insert any values I like into your database. I can't believe 
>>>>the JDBC classes don't provide 
>>>>
>>>>1. Some way to escape value strings
>>>>2. Some form of placeholders to deal with this
>>>>
>>>> 
>>>>
>>>>      
>>>>
>>>-- 
>>>
>>>
>>>Charles H. Woloszynski
>>>
>>>ClearMetrix, Inc.
>>>115 Research Drive
>>>Bethlehem, PA 18015
>>>
>>>tel: 610-419-2210 x400
>>>fax: 240-371-3256
>>>web: www.clearmetrix.com
>>>
>>>
>>>
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>
>>>    
>>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>>http://archives.postgresql.org
>>  
>>
>
>-- 
>
>
>Charles H. Woloszynski
>
>ClearMetrix, Inc.
>115 Research Drive
>Bethlehem, PA 18015
>
>tel: 610-419-2210 x400
>fax: 240-371-3256
>web: www.clearmetrix.com
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>