pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow does postgres handle non literal string values
8+ messages / 6 participants
[nested] [flat]
* How does postgres handle non literal string values
@ 2002-11-25 12:57 javaholic <monroy@mindspring.com>
0 siblings, 2 replies; 8+ messages in thread
From: monroy@mindspring.com @ 2002-11-25 12:57 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How does postgres handle non literal string values
@ 2002-11-26 15:12 Henshall, Stuart - Design & Print <SHenshall@westcountry-design-print.co.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Henshall, Stuart - Design & Print @ 2002-11-26 15:12 UTC (permalink / raw)
To: 'monroy@mindspring.com' <monroy@mindspring.com>; pgsql-sql
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>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How does postgres handle non literal string values
@ 2002-11-26 15:35 Richard Huxton <dev@archonet.com>
parent: javaholic <monroy@mindspring.com>
1 sibling, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2002-11-26 15:35 UTC (permalink / raw)
To: monroy@mindspring.com; pgsql-sql
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 =
> "INSERT INTO \"login\" (\'user\', \'password\')
> VALUES ('username', 'password')";
You're creating an insertString with the literal words "username" and
"password" in them. This is a java issue, not a PG issue. You'll want
something like (sorry if syntax is wrong, but I don't do Java):
String insertString = "Insert INTO ... VALUES ('" + sql_escaped(username) +
"')..."
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
--
Richard Huxton
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How does postgres handle non literal string values
@ 2002-11-26 16:05 Charles H. Woloszynski <chw@clearmetrix.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Charles H. Woloszynski @ 2002-11-26 16:05 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: javaholic <monroy@mindspring.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How does postgres handle non literal string values
@ 2002-11-26 17:18 Rob Hills <rob@netpaver.com.au>
parent: javaholic <monroy@mindspring.com>
1 sibling, 0 replies; 8+ messages in thread
From: Rob Hills @ 2002-11-26 17:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How does postgres handle non literal string values
@ 2002-12-04 05:22 Vernon Wu <vernonw@gatewaytech.com>
parent: Charles H. Woloszynski <chw@clearmetrix.com>
0 siblings, 1 reply; 8+ messages in thread
From: Vernon Wu @ 2002-12-04 05:22 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How does postgres handle non literal string values
@ 2002-12-04 13:00 Charles H. Woloszynski <chw@clearmetrix.com>
parent: Vernon Wu <vernonw@gatewaytech.com>
0 siblings, 1 reply; 8+ messages in thread
From: Charles H. Woloszynski @ 2002-12-04 13:00 UTC (permalink / raw)
To: vernonw@gatewaytech.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How does postgres handle non literal string values
@ 2002-12-04 17:11 Vernon Wu <vernonw@gatewaytech.com>
parent: Charles H. Woloszynski <chw@clearmetrix.com>
0 siblings, 0 replies; 8+ messages in thread
From: Vernon Wu @ 2002-12-04 17:11 UTC (permalink / raw)
To: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2002-12-04 17:11 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-25 12:57 How does postgres handle non literal string values javaholic <monroy@mindspring.com>
2002-11-26 15:35 ` Richard Huxton <dev@archonet.com>
2002-11-26 16:05 ` Charles H. Woloszynski <chw@clearmetrix.com>
2002-12-04 05:22 ` Vernon Wu <vernonw@gatewaytech.com>
2002-12-04 13:00 ` Charles H. Woloszynski <chw@clearmetrix.com>
2002-12-04 17:11 ` Vernon Wu <vernonw@gatewaytech.com>
2002-11-26 17:18 ` Rob Hills <rob@netpaver.com.au>
2002-11-26 15:12 Re: How does postgres handle non literal string values Henshall, Stuart - Design & Print <SHenshall@westcountry-design-print.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox