January 31st, 2000, 10:20 AM
Hi, I am trying to insert data into a SQL Server database. I have a problem when the data contains quotes in the text, say like in "SAM's Club". The trouble is when I am building the SQL insert statement , the statement is terminating at the quote after 'SAM' and so the insert statement fails.
One way is parsing the input data and checking whether a quote exists, but how do I ensure that the field is inserted along with the quotes. Please verify the servlet below and suggest ideas.
Thanks
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.net.*;
import java.sql.*;
public class Cont_Insert extends HttpServlet
{
public String StrDriver,StrConnect,StrUsername,StrPassword;
public String Str_first_name = "", Str_init, Str_last_name;
Connection ConGen;
Statement StGen;
ResultSet RsetSearch = null;
int IntPubno_records, IntCount = 0, IntExistCode = 0;
String StrSQLInsert = ""; //SQL for inserting the database
public String StrHtml="", StrUpdresult="";
PrintWriter PWriter;
protected void service(HttpServletRequest SResq,HttpServletResponse SResp) throws ServletException , IOException
{
PWriter=new PrintWriter(SResp.getOutputStream());
SResp.setContentType("text/html");
StrConnect = //give connect details;
StrDriver = //driver details;
StrUsername = "";
StrPassword = "";
//if any or all of the 3 fields which are submitted from a form contain a quote
//how do you ensure that it is inserted in the database ?
Str_first_name = SResq.getParameter("first_name");
if ( Str_first_name == null ) Str_first_name = "";
Str_init = SResq.getParameter("init");
if ( Str_init == null ) Str_init = "";
Str_last_name = SResq.getParameter("last_name");
if ( Str_last_name == null ) Str_last_name = "";
//Get the DB Connection.....
try
{
Class.forName(StrDriver);
ConGen = DriverManager.getConnection(StrConnect,StrUsername,StrPassword);
StGen = ConGen.createStatement();
}
catch(Exception exep)
{
StrHtml += "\nException shown is : DataBase Connection Failed... " ;
}
StrSQLInsert += " insert into t_names (mc_last_nam, mc_first_nam, mc_init)";
StrSQLInsert += " VALUES ( ";
StrSQLInsert += "'" + Str_last_name + "', '" + Str_first_name + "', ";
StrSQLInsert += "'" + Str_init + "')";
}
//fails if Str_last_name or other strings have quotes in them.
try
{
StGen.execute(StrSQLInsert);
IntPubno_records = StGen.getUpdateCount();
StrUpdresult = "";
if (IntPubno_records == 1)
{
StrUpdresult += "\n <b>" + Str_last_name + ", " + Str_first_name + " updated in the database. </b>";
}
else
{
StrUpdresult += "\n <b> Error occured while updating the database...Contact Admin </b>";
}
}
catch(Exception exp)
{
StrHtml += "\n Insertion failed." + exp;
}
StrHtml += "<Html><Head><Title>Contacts </Title></Head>\n";
StrHtml += "<Body bgcolor='white'> ";
StrHtml += "<center>" + StrUpdresult + " </center> <hr> </Body><BR>";
StrHtml += "
<hr>\n";
StrHtml += "<center>\n";
StrHtml += "<font face=Arial size=1>\n";
StrHtml += "</font>\n";
StrHtml += "</center>
\n";
StrHtml += "</Html>";
PWriter.println(StrHtml);
PWriter.flush();
StrHtml="";
RsetSearch = null;
IntCount = 0;
StrUpdresult = "";
StrSQLInsert = "";
PWriter.close();
try
{
ConGen.close(); ///Closing the database connection...
}
catch(SQLException SQLexp)
{
StrHtml += "Exception is : " + SQLexp + "\n";
}
}
}
One way is parsing the input data and checking whether a quote exists, but how do I ensure that the field is inserted along with the quotes. Please verify the servlet below and suggest ideas.
Thanks
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.net.*;
import java.sql.*;
public class Cont_Insert extends HttpServlet
{
public String StrDriver,StrConnect,StrUsername,StrPassword;
public String Str_first_name = "", Str_init, Str_last_name;
Connection ConGen;
Statement StGen;
ResultSet RsetSearch = null;
int IntPubno_records, IntCount = 0, IntExistCode = 0;
String StrSQLInsert = ""; //SQL for inserting the database
public String StrHtml="", StrUpdresult="";
PrintWriter PWriter;
protected void service(HttpServletRequest SResq,HttpServletResponse SResp) throws ServletException , IOException
{
PWriter=new PrintWriter(SResp.getOutputStream());
SResp.setContentType("text/html");
StrConnect = //give connect details;
StrDriver = //driver details;
StrUsername = "";
StrPassword = "";
//if any or all of the 3 fields which are submitted from a form contain a quote
//how do you ensure that it is inserted in the database ?
Str_first_name = SResq.getParameter("first_name");
if ( Str_first_name == null ) Str_first_name = "";
Str_init = SResq.getParameter("init");
if ( Str_init == null ) Str_init = "";
Str_last_name = SResq.getParameter("last_name");
if ( Str_last_name == null ) Str_last_name = "";
//Get the DB Connection.....
try
{
Class.forName(StrDriver);
ConGen = DriverManager.getConnection(StrConnect,StrUsername,StrPassword);
StGen = ConGen.createStatement();
}
catch(Exception exep)
{
StrHtml += "\nException shown is : DataBase Connection Failed... " ;
}
StrSQLInsert += " insert into t_names (mc_last_nam, mc_first_nam, mc_init)";
StrSQLInsert += " VALUES ( ";
StrSQLInsert += "'" + Str_last_name + "', '" + Str_first_name + "', ";
StrSQLInsert += "'" + Str_init + "')";
}
//fails if Str_last_name or other strings have quotes in them.
try
{
StGen.execute(StrSQLInsert);
IntPubno_records = StGen.getUpdateCount();
StrUpdresult = "";
if (IntPubno_records == 1)
{
StrUpdresult += "\n <b>" + Str_last_name + ", " + Str_first_name + " updated in the database. </b>";
}
else
{
StrUpdresult += "\n <b> Error occured while updating the database...Contact Admin </b>";
}
}
catch(Exception exp)
{
StrHtml += "\n Insertion failed." + exp;
}
StrHtml += "<Html><Head><Title>Contacts </Title></Head>\n";
StrHtml += "<Body bgcolor='white'> ";
StrHtml += "<center>" + StrUpdresult + " </center> <hr> </Body><BR>";
StrHtml += "
<hr>\n";
StrHtml += "<center>\n";
StrHtml += "<font face=Arial size=1>\n";
StrHtml += "</font>\n";
StrHtml += "</center>
\n";
StrHtml += "</Html>";
PWriter.println(StrHtml);
PWriter.flush();
StrHtml="";
RsetSearch = null;
IntCount = 0;
StrUpdresult = "";
StrSQLInsert = "";
PWriter.close();
try
{
ConGen.close(); ///Closing the database connection...
}
catch(SQLException SQLexp)
{
StrHtml += "Exception is : " + SQLexp + "\n";
}
}
}