avoid duplicate single quotation mark

[borna]

As soon as I insert an expression which contains a single quotation mark (') and I try to updte the writen later on, I see that it has become duplicated ('').

I would be very glad for any help!

[r937]

which language are you using? php? asp? perl?

[borna]

I use Java with MsSql 2000 as RDBMS.

[r937]

i'll move this to the java forum then

[hooknc]

We might need a little more information then what you've provided.

Could you show us some code?

[borna]

This is the code:

public String insert(String Table,int ciu)
    {
        try
        {           
            JDBCconnection jdbcconnection = new JDBCconnection();
            formatForSQL();
            jdbcconnection.setConnection();
            String s = null;
                        
            if(ciu==0){
                s = "Insert into "+ Table + " (mailSession, from_id,to_id,subject,body,date_time,countAttachedFiles,priority,size) values (";            
                s+=""+mailSession+",";
                s+="'"+from_id+"',";
                s+="'"+to_id+"',";
                s+="'"+subject+"',";
                s+="'"+body+"',";                        
                s+="'"+date_time+"',";            
                s+=""+countAttachedFiles+",";
                s+="'"+priority+"',";
                s+=""+size+")";
                System.out.println(s);
                jdbcconnection.submitUpdateQuery(s);
            }
            else{
                s = "Update "+ Table + " set ";            
                s+="mailSession='"+mailSession+"',";
                s+="from_id='"+from_id+"',";
                s+="to_id='"+to_id+"',";
                s+="subject='"+subject+"',";
                s+="body="+body+",";
                s+="date_time='"+date_time+"',";
                s+="countAttachedFiles='"+countAttachedFiles+"',";
                s+="priority='"+priority+"',";
                s+="size='"+size+"'";
                s+=" where ";
                s+=" id="+id;
                System.out.println(s);
                jdbcconnection.submitUpdateQuery(s);
            }
            jdbcconnection.disconnectFromDbase();
            return"";
        }
        catch(SQLException sqlexception)
        {
            System.out.println(sqlexception.toString());
            return(sqlexception.getMessage());

        }
    }

[rushiku]

I don't see a logic issue in the code you provided, how about showing us formatForSQL()?

[hooknc]

I didn't look at the posted code very closely, but you might want to consider using PreparedStatements instead of creating all the sql by hand.

PreparedStatement will manage all the correct single and double quotes for you.

Best of Luck.

[borna]

formatForSQL()?

public void formatForSQL() {
        from_id= util.formatForSQL(String.valueOf(from_id));       
        to_id= util.formatForSQL(String.valueOf(to_id));       
        countAttachedFiles= Integer.parseInt(util.formatForSQL(String.valueOf(countAttachedFiles)));       
        subject = util.formatForSQL(subject);        
        body = util.formatForSQL(body);      
        date_time = util.formatForSQL(date_time);
        priority = util.formatForSQL(priority);
    }

[borna]

PreparedStatement will manage all the correct single and double quotes for you.

Could you please provide some code. Thanks in advance!

[rushiku]

Using Prepared Statements

[hooknc]

Thanks for jumping on that grenade rushiku.

That link is an excellent overview on PreparedStatements.

PreparedStatements are also useful for stopping/preventing SQL injection attacks.

[borna]

A version of JDBCConnection that I use is as follows:

import java.io.PrintStream;
import java.sql.*;
import java.util.Enumeration;

public class JDBCconnection
{

    public JDBCconnection()
    {

        b = "com.jnetdirect.jsql.JSQLDriver";
        user="sa";
        pass="1234abcd";
        d="VLMS";        
        c = "jdbc:JSQLConnect://127.0.0.1:1433/database="+d+"&user="+user;
    }        

    public void setConnection()
    {
        try
        {
            Class.forName(b).newInstance();
        }
        catch(ClassNotFoundException _ex)
        {
            System.err.println("ERROR : JdbcConnect(init) :jdbc class not found \n");
        }
        catch(IllegalAccessException _ex)
        {
            System.err.println("ERROR : JdbcConnect(init) :jdbc illegal exception ");
        }
        catch(InstantiationException _ex)
        {
            System.err.println("ERROR : JdbcConnect(init) :jdbc instatiate exception ");
        }
        try
        {
            a = DriverManager.getConnection(c, user, pass);
        }
        catch(SQLException sqlexception)
        {
            System.err.println((new StringBuilder()).append("ERROR : dbase(connect_to_dbase) :can't connect to databse ,BCZ:").append(sqlexception.getSQLState()).append(sqlexception.getMessage()).append("").append(c).toString());
        }
        try
        {
            a.setAutoCommit(true);
            return;
        }
        catch(SQLException _ex)
        {
            System.err.println("ERROR : dbase(connect_to_dbase) :Autocommit faile ");
        }
    }

    public ResultSet submitExecuteQuery(String s)
        throws SQLException
    {
        if(a == null)
            setConnection();
        Statement statement;
        try
        {
            statement = a.createStatement();
        }
        catch(SQLException _ex)
        {
            setConnection();
            try
            {
                statement = a.createStatement();
            }
            catch(SQLException sqlexception1)
            {
                System.err.println("Error: dbase(submitQuery): createStatement failed ");
                throw sqlexception1;
            }
        }
        ResultSet resultset;
        try
        {
            resultset = statement.executeQuery(s);
        }
        catch(SQLException sqlexception)
        {
        System.err.println((new StringBuilder()).append("Error: dbase(submitQuery): execute query failed: ").append(sqlexception).append("\nQuery:").append(s).toString());
            throw sqlexception;
        }
        return resultset;
    }

    public void submitUpdateQuery(String s)
        throws SQLException
    {
        if(a == null)
            setConnection();
        Statement statement;
        try
        {
            statement = a.createStatement();
        }
        catch(SQLException _ex)
        {
            setConnection();
            try
            {
                statement = a.createStatement();
            }
            catch(SQLException sqlexception1)
            {
                System.err.println("Error: dbase(submitQuery): createStatement failed - servlet exiting");
                throw sqlexception1;
            }
        }
        try
        {
            statement.executeUpdate(s);
            return;
        }
        catch(SQLException sqlexception)
        {
            System.err.println((new StringBuilder()).append("Error: dbase(submitQuery): execute query failed: ").append(sqlexception).append("\nQuery:").append(s).toString());
            throw sqlexception;
        }
    }

    public void disconnectFromDbase()
    {
        if(a == null)
        {
            System.err.println("Error: dbase(disconnect_form_dbase) attempting to close a null connection");
            return;
        }
        try
        {
            a.close();
            a = null;
            return;
        }
        catch(SQLException sqlexception)
        {
            System.err.println((new StringBuilder()).append("Error: dbase(disconnect_from_dbse) attempting to close connection :").append(sqlexception).toString());
        }
    }        

    Connection a;
    String b;
    String c;
    String user;
    String pass;
    String d;
}

Now since

PreparedStatement update= jdbcconnection.prepareStatement(updateQuery);

So that I need to have the method prepareStatement in my JDBC.

Is that right? If so let me know of any solutions please.

[borna]

So what do you suggest!

[hooknc]

There are a few issues here...

- None of the exceptions above are re-thrown. My guess is that if something goes wrong then the application should let you know right away instead of failing "silently".

- With the way the exceptions are being caught you can still continue down your code path. For example if the classname part of the code fails then you're still going to attempt to get a connection.

- Your code isn't thread safe. For example, the method setException is called from two other methods and one thread could interfere with the other thread.

- Your code is quite coupled together. One of the goals of OO programming is HIGH cohesion and LOW coupling. This class does everything: attempts to create the driver class, open the connection, create the prepared statement, close the connection, etc... That is a lot of work for one class. Consider making a connection pool interface that has a simple implementation class that is access via a Factory.

public interface ConnectionPool {
  Connection getConnection();
  void releaseConnection(Connection connection);
}

public class ConnectionPoolImpl implements ConnectionPool {
  pulbic ConnectionPoolImpl(String classname, String url, String username, String password) throws Exception {
    Class.forName(classname);
  }

  public Connection getConnection() {
    return DriverManager(url, username, password);
  }

  public void releaseConnection(Connection connection) {
    connection.close();
  }
}

public ConnectionPoolFactory {
  private ConnectionPoolFactory connectionPoolFactory;
  private ConnectionPool connectionPool;
  // private constructor because of being a singlton.
  private ConnectionPoolFactory() {
    this.connectionPool = new ConnectionPoolImpl();
  }

  public static ConnectionPoolFactory getInstance() {
    if(connectionPoolFactory == null) {
      connectionPoolFactory = new ConnectionPoolFactory();
    }
    return connectionPoolFactory;
  }
  public ConnectionPool getConnectionPool() {
    return this.connectionPool;
  }
}

The above isn't the best code and most likely should not be used in a production environment, but should kind of show you some examples of using a connection pool and a factory.

Then you should create a data access object (DAO) of some sort for executing your sql code.

public void SomeDAO {
  public void updateTable(String arg0, String arg1) throws Exception {
    Connection connection = ConnectionPoolFactory.getInstance().getConnection();

    PreparedStatement preparedStatement = connection.prepareStatement("update user set username = ?, set password = ?;
    preparedStatement.setString(arg1);
    preparedStatement.setString(arg2);

    preparedStatement.executeUpdate();
  }
}

Whew, that is a lot...

Database coding is quite difficult and you might want to consider using a framework like hibernate to help simplify your code.

Best of Luck.