Issues with apostrophe symbol as part of document name

Problem Statement:

quoted string not properly terminated.

Ok, what is the bug here?

Application is not getting the documents which are related to the specific model.

How the problem was identified?

I have gone through the log files to know the origin of the problem.  Log file is getting updated with the above specified error.  Later, I have tried to replicate the same issue with a sample program.  Finally, I came to know that the issue is related to Java’s Statement interface.  If we want to use apostrophe in middle of SQL statement, we should specify another apostrophe.  If we prepare SQL statement with Java's Statement interface, we should care about this apostrophe issue.  If we use Java's PreparedStatement instead of Java's Statement, we need not worry about this issue.  Java takes care about this.

Solution provided:

Quick solution provided:

I have updated the database by removing the special character.

Suggested solution: I have updated the java code as below.

Previous Code: -

Statement stmt = con.createStatement();

String sQueryDocData = "SELECT * FROM DOC_ATTACHMENTS WHERE DOC_ID = "+ docId +" AND DOC_ATTACH_NAME = '" + sDocAttchName +"'";

ResultSet rs = stmt.executeQuery(sQueryDocData);

Updated Code: -

PreparedStatement pstmt = con.prepareStatement("SELECT * FROM DOC_ATTACHMENT WHERE DOC_ID = ? AND DOC_ATTACH_NAME = ?");

pstmt.setInt(1, docId);

pstmt.setString(2, sDocAttchName);

ResultSet rs = pstmt.executeQuery();

For Example: - String sDocAttachName = "doc's.txt";

Conflicts between Employee Names in DataBase and Web-Services

Problem Statement:
One of the end-users is not able to see the DOCs which were assigned to her.

Ok, what is the bug here?
DOCs are not appearing to them.

How the problem was identified?

I had a discussion with that specific end-user who is unable to see her DOCs.  I came to know that she is facing this problem recently after one incident, i.e. her name was slightly updated by HR department.  Before that update, she has seen all her DOCs.  First, I have verified the Database whether the DOCs are available to that person. Yes.  DOCs are available to that person.  Later, I have gone through the code to find out the origin of getting Employee names.  Database is having the employee names in the format of Emp_Name(Emp_ID).  When ever the end-user login the application, application is getting her Emp-Name and Emp-ID from two different webservices and making them in the format of Emp_Name(Emp_ID) and verifying the same with Database to identify the DOCs related to that person.  By observing the log files, I came to know that the both names are mismatching in case of this end-user.  Because, Database is having old name and web-service is giving updated name.

Solution provided:

Quick solution provided:
I have updated the Database with new name.  Now, both names are matched and end-user able to see the DOCs.

Suggested solution:
SQL statements are updated slightly from Emp_Name(Emp_ID) to %(Emp_ID).  Now, SQL statement is getting the DOCs by verifying Emp_ID instead of verifying the entire format Emp_Name(Emp_ID).

i18n : JSP and MySQL Internationalization

I) JSP Character Set Handling

Character handling can be split into two categories: displaying the characters and receiving the ones entered by the user.  For JSP pages, setting the encoding for each category is done separately by using different directives and/or functions.

Displaying International Characters

i. To set the character encoding for JSP page display, use the standard “page” directive with “contentType” parameter as follows:

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>

Page directive is used to control the structure of a servlet or a JSP by importing classes, customizing superclasses, and setting the content type, etc.

ii. The following HTML tag can be used for the web browser to load the correct character set:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

Meta tags with an http-equiv attribute are the same as HTTP headers. In general, they are used to control the action of browsers, and can be used to refine the information provided by the actual headers.

iii. Avoid using Java String functions (or constructors) such as the following to convert character encoding of a string, because it is both inefficient and unnecessary. Once you set all the options mentioned in this article correctly, you will not need to use such a conversion.

str = new String(request.getParameter("value").getBytes("ISO-8859-1"), "UTF-8");

iv. On every request, you have to set the encoding of characters manually; it is best to create a filter that can be called for every action by specifying it in web.xml:

CharacterEncodingFilter.java

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.ServletException;
import javax.servlet.Filter;
import javax.servlet.FilterConfig;
import javax.servlet.FilterChain;

import java.io.IOException;

public class CharacterEncodingFilter implements Filter
{

    private FilterConfig fc;

    public void doFilter(ServletRequest req, ServletResponse res, FilterChain chain) throws IOException, ServletException
    {

        HttpServletRequest request = (HttpServletRequest) req;
        HttpServletResponse response = (HttpServletResponse) res;

        response.setContentType("text/html; charset=UTF-8");
        request.setCharacterEncoding("UTF8");

        chain.doFilter(request, response);        //do it again, since JSPs will set it to the default

        response.setContentType("text/html; charset=UTF-8");
        request.setCharacterEncoding("UTF8");
    }

    public void init(FilterConfig filterConfig)
    {

        this.fc = filterConfig;
    }

    public void destroy()
    {

        this.fc = null;
    }
}


web.xml


<filter>
  <filter-name>CharacterEncodingFilter</filter-name>
  <filter-class>com.its.struts.action.CharacterEncodingFilter</filter-class>
</filter>

<filter-mapping>
  <filter-name>CharacterEncodingFilter</filter-name>
  <servlet-name>action</servlet-name>
</filter-mapping>

<filter-mapping>
  <filter-name>CharacterEncodingFilter</filter-name>
  <url-pattern>*.jsp</url-pattern>
</filter-mapping>

<filter-mapping>
  <filter-name>CharacterEncodingFilter</filter-name>
  <url-pattern>*.html</url-pattern>
</filter-mapping>

ACTION class needs to be specified for the STRUTS application.
Other than STRUTS, we can specify all the servlets to handle all requests.

<filter-mapping>
  <filter-name>CharacterEncodingFilter</filter-name>
  <url-pattern>/servlets/*</url-pattern>
</filter-mapping>


v. Java Mail : Finally comes the easiest part: sending e-mails with the subject and body in UTF-8.  The only things you have to do here is use MimeMessage, and give additional parameters when setting the subject and text of your message:

(…)
MimeMessage msg = new MimeMessage(session);
msg.setFrom(InternetAddress.parse(from, false)[0]);
msg.setSentDate(new Date());
msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse(to, false));
msg.setSubject(subject, "UTF-8");
msg.setText(body, "UTF-8");
transport.sendMessage(msg, msg.getAllRecipients());




II) For the data entered into and received from MySQL database server, you can set the default character set and collation at five levels: (i) server, (ii) database, (iii) table, (iv) column, and (v) connection.  More information and example for each follows:

i. When you start the database server: We can add the following lines in my.ini file to initialize the settings on database startup.

default-collation=utf8
collation_server=utf8_unicode_ci
character_set_server=utf8
default-character-set=utf8


ii. When you are creating the database (or with alter statement after creation):

CREATE DATABASE db_name
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

iii. When you are creating the table (or with alter statement after creation):

CREATE TABLE tbl_name (column_list)
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

iv. When you are describing the columns during table creation:

CREATE TABLE tbl_name
(
clm_name VARCHAR(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci
);

v. When you are creating the connection:

conn.createStatement().execute(" SET NAMES 'utf8' ");

i18n

Simple Java Program to get Database ( ORACLE / MYSQL / DB2 ) Connection

//  DBConnect.java     -   DataBase ( ORACLE / MYSQL / DB2 ) Connection Sample Program   


import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnect
{

       public static void main(String args[])
       {

Connection con = null;
Statement stmt = null;
   PreparedStatement pstmt = null;
ResultSet res = null;
   boolean found=false;

try
{

                    //          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");     // Oracle
           // Class.forName("org.gjt.mm.mysql.Driver");
Class.forName("com.mysql.jdbc.Driver");                                 //  My SQL
           //  Class.forName("com.ibm.db2.jcc.DB2Driver");             //  DB2

//      con = DriverManager.getConnection("jdbc:odbc:DataSourceName","scott","tiger");
// con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?user=root&password=");
// con = DriverManager.getConnection("jdbc:db2://localhost:50000/db2db", "db2", "db2");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "root");

  stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_UPDATABLE);
res = stmt.executeQuery("select sysdate from dual");


// pstmt=conn.prepareStatement("select sysdate from dual",
//                      ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY );
// rset=pstmt.executeQuery();

if(rset!=null)
{

if(rset.next())
{

found=true;
System.out.println("System Date: "+rset.getString(1));
}

rset.beforeFirst();

if(rset.next())
{

found=true;
System.out.println("System Date: "+rset.getString(1));
}
}

if (found ==false)
{

System.out.println("No Information Found");
}
}
catch(Exception e)
{

System.out.println(e);

    e.printStackTrace();

}

finally
{

try
{

res.close();
stmt.close();
con.close();
}
catch(Exception e)
{

System.out.println(e);
}

res = null;
stmt = null;
con = null;
}
}
}

Commands to execute the program:

MYSQL:

PATH=%PATH%;C:\Java\jdk1.6.0_11\bin
set CLASSPATH=%CLASSPATH%;mysql-connector-java-3.0.15-ga-bin.jar
javac  DBConnect.java
java    DBConnect


DB2:


javac -classpath .;db2jcc.jar;db2jcc_license_cu.jar DBConnect.java
java -classpath .;db2jcc.jar;db2jcc_license_cu.jar DBConnect

Drawback of PreparedStatement.....?

select * from TABLE1 where COL_ID in ( 9489215, 9489216) 

 Here, we can not assign the values 9489215, 9489216.