Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JSP Query

 
Joseph Maina
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Am having trouble posting string data to mysql and displaying it correctly in a browser. I have a JSP, which takes input from a user. The data is then posted to Mysql database. If the string has an apostrophe or double quotes, the Mysql throws an error. How do I solve this issue since the user might put those characters that might generate an error? Also how do I display such data which has such characters correctly if I manage to post it with the mysql escape characters?
 
E Armitage
Rancher
Posts: 989
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How are you putting the values into the database? If you are using JDBC then you should use a PreparedStatement because
1.) It will escape the special characters for you
2.) It protects you from SQL injection where a user can provide input like "input;delete from transactions;drop table users;" e.t.c
3.) Some caching is done which makes future executions faster.

You also are hopefully not doing the database code in a JSP. It should be a normal Java class called from a servlet.
 
Joseph Maina
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks. Am using <private Statement statement;>. Should I change this to < private PreparedStatement statement;> and the issue will be resolved? If there is some more coding needed in order to use the PreparedStatement, would you kindly assist?

Am not doing the database code in a JSP but in a Java class called as a bean through JSP useBean functionality.
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Should I change this to < private PreparedStatement statement;> and the issue will be resolved?

No, that's not sufficient. Read http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html to learn all about it.
 
Joseph Maina
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I appreciate.
Best regards.
 
Joseph Maina
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is my code:



Is there something wrong with the logic of the code?
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What leads you to believe that anything is wrong with the code? It certainly is rather verbose for something as basic as executing a single SQL statement, but that shouldn't impact its functionality. But it does make it harder to read and spot problems.

I notice that you're not using the PreparedStatement correctly - you're not setting any parameters.
 
Joseph Maina
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What parameters would that be and how do I set them?
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know what parameters your query has, but you mentioned that it includes input from users, so it must have at least one. The article I linked to describes how to set parameters in PreparedStatements.
 
J. Kevin Robbins
Bartender
Posts: 1742
19
Chrome Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joseph Maina wrote:What parameters would that be and how do I set them?

The values that you want to insert. Don't pass the entire SQL string.

Let me take a shot at this. First, what you have here is not a bean, it's a DAO, Data Access Object. Beans are used to pass data around. They just have fields with getters and setters.

Here's how I typically setup a DAO. Define the SQL for your insert statement.


The values are going to be passed to your DAO. They could be passed individually or in a collection. Whatever. For this example let's say you passed in a bean called empBean. Setup your connection whatever way works for your. As Ulf pointed out yours is rather verbose, but if that works for you that's fine. Now for the Statement. We'll assume you already have a connection called "connection".


Each of those "setString" calls corresponds to one of the question marks in the PreparedStatement. They have to match in number and data type. If your SQL string has 12 questions marks you have to do 12 "set" methods. If the database data type for that field is an int, then you do a setInt.

Returning the result code is optional, but it provides a way for the servlet to make sure everything went okay and provide some feedback to the user. I often evaluate it to display a simple message on the page such as "Record inserted successfully" or something like that.

I hope this helps.
 
Joseph Maina
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@ J. Kevin Robbins;

Thanks Robbins. That sets things into perspective, especially for starters like me.

The challenge I have is that I have various JSPs posting data to different tables in a database each with its unique schema. I needed a single DAO that can handle all the posting without having to define each string variable for each database table, like in the example given; private final static String INSERT_EMPLOYEE_RECORD . Is there a way to do this or I have to define the SQL insert statement for each table?
 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I advise to keep DB statements separate for each table. They can still be part of the same DAO.
 
J. Kevin Robbins
Bartender
Posts: 1742
19
Chrome Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can define as many SQL statements as you want. My DAO's typically will have a statement for inserts, another for deletes, another for updates, and then perhaps several selects such as one to select all records and one to select a single record and one to select records within a given date range. Usually each of these will be called by a separate method because each will have different parameters.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic