SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Evangelist borna's Avatar
    Join Date
    Jun 2006
    Location
    Iran, Tehran
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation avoid duplicate single quotation mark

    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!
    -1/2=999?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    which language are you using? php? asp? perl?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist borna's Avatar
    Join Date
    Jun 2006
    Location
    Iran, Tehran
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use Java with MsSql 2000 as RDBMS.
    -1/2=999?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i'll move this to the java forum then

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SiteP0int Weazle hooknc's Avatar
    Join Date
    Dec 2004
    Location
    Socialist Republic of Boulder
    Posts
    937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We might need a little more information then what you've provided.

    Could you show us some code?
    baby steps... baby steps...

  6. #6
    SitePoint Evangelist borna's Avatar
    Join Date
    Jun 2006
    Location
    Iran, Tehran
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the code:
    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());
    
            }
        }
    -1/2=999?

  7. #7
    SitePoint Wizard silver trophy rushiku's Avatar
    Join Date
    Dec 2003
    Location
    A van down by the river
    Posts
    2,056
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    I don't see a logic issue in the code you provided, how about showing us formatForSQL()?

  8. #8
    SiteP0int Weazle hooknc's Avatar
    Join Date
    Dec 2004
    Location
    Socialist Republic of Boulder
    Posts
    937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    baby steps... baby steps...

  9. #9
    SitePoint Evangelist borna's Avatar
    Join Date
    Jun 2006
    Location
    Iran, Tehran
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    formatForSQL()?
    Code:
    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);
        }
    -1/2=999?

  10. #10
    SitePoint Evangelist borna's Avatar
    Join Date
    Jun 2006
    Location
    Iran, Tehran
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PreparedStatement will manage all the correct single and double quotes for you.
    Could you please provide some code. Thanks in advance!
    -1/2=999?

  11. #11
    SitePoint Wizard silver trophy rushiku's Avatar
    Join Date
    Dec 2003
    Location
    A van down by the river
    Posts
    2,056
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

  12. #12
    SiteP0int Weazle hooknc's Avatar
    Join Date
    Dec 2004
    Location
    Socialist Republic of Boulder
    Posts
    937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    baby steps... baby steps...

  13. #13
    SitePoint Evangelist borna's Avatar
    Join Date
    Jun 2006
    Location
    Iran, Tehran
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A version of JDBCConnection that I use is as follows:
    Code:
    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
    Code:
    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.
    -1/2=999?

  14. #14
    SitePoint Evangelist borna's Avatar
    Join Date
    Jun 2006
    Location
    Iran, Tehran
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So what do you suggest!
    -1/2=999?

  15. #15
    SiteP0int Weazle hooknc's Avatar
    Join Date
    Dec 2004
    Location
    Socialist Republic of Boulder
    Posts
    937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.

    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.
    baby steps... baby steps...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •