Python sqlite3.Error() Examples

def _DetectApplicationUsageTable(self, conn):
    """Detect whether the application usage table exists.

    Args:
      conn: sqlite3.Connection object
    Returns:
      True if the table exists, False if not.
    Raises:
      sqlite3.Error: if error occurs
    """
    try:
      conn.execute(APPLICATION_USAGE_TABLE_DETECT)
      exists = True
    except sqlite3.OperationalError, e:
      if e.args[0].startswith('no such table'):
        exists = False
      else:
        raise 

def VerifyDatabase(self, fix=False):
    """Verify database integrity."""
    conn = self._Connect()
    try:
      q = conn.execute(APPLICATION_USAGE_TABLE_SELECT)
      unused_rows = q.fetchall()
      ok = True
    except sqlite3.Error:
      ok = False

    if not ok:
      if fix:
        print 'Recreating database.'
        print 'Recovered %d rows.' % self._RecreateDatabase()
      else:
        print 'Database is malformed.  Run with --fix to attempt repair.'
    else:
      print 'Database is OK.' 

def __connect(self):
        """
        Private function for connecting and setting return type for select ops.
        Raises a DbConnectionError when fails to connect.
        """
        if self.conn:
            logging.info("AuthorizationDataManager: Reconnecting to %s on request", self.db_path)
            self.__close()

        try:
            self.conn = sqlite3.connect(self.db_path)
        except sqlite3.Error as e:
            logging.error("Failed to connect to DB (%s): %s", self.db_path, e)
            raise DbConnectionError(self.db_path)

        # Use return rows as Row instances instead of tuples
        self.conn.row_factory = sqlite3.Row 

def sqlBeginRead(conn):
    """
    Starts a read by executing SQL and returning a cursor

    :param conn: Database connection
    :return: cursor or None if we encountered a problem
    """

    sql = "SELECT KEYID, PORT, BASE64, EPOCH FROM PROXY"
    cursor = None

    try:
        # Use connection as context manager to rollback automatically if error
        with conn:
            cursor = conn.cursor()
            cursor.execute(sql)

    except sqlite3.Error as e:
        logger.error("Sqlite3.Error: " + str(e))
        conn.close()
        return None

    return cursor 

def insert_new_command(db_conn, plugin_name, command_name, permission_level, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO commands(plugin_id, name, level)
            VALUES (
            (SELECT plugins.plugin_id FROM plugins WHERE plugins.name = ? LIMIT 1),
            ?,
            ?
            );
        """
        try:
            db_conn.cursor().execute(table_query, (plugin_name, command_name, permission_level))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new command into the database: {plugin_name}-{command_name}-{permission_level}", origin=L_DATABASE)
                log(INFO, f"Inserted new command into the database: {plugin_name}-{command_name}-{permission_level}", origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

def insert_new_plugins_help(db_conn, plugin_name, help_text, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO plugins_help(plugin_id, help_text)
            VALUES (
            (SELECT plugins.plugin_id FROM plugins WHERE plugins.name = ? LIMIT 1),
            ?
            );
        """
        try:
            db_conn.cursor().execute(table_query, (plugin_name, help_text))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new plugin help data into the database: {plugin_name}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new plugin help data into the database: {plugin_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

def insert_new_alias(db_conn, alias_name, commands, ignore_file_save=False) -> bool:
        table_query = f"""
                INSERT INTO aliases(name, alias)
                VALUES (
                ?,
                ?
                );
            """
        try:
            db_conn.cursor().execute(table_query, (alias_name, commands))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new alias into the database: {alias_name}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new alias into the database: {alias_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

def insert_new_plugin(db_conn, plugin_name, ignore_file_save=False) -> bool:
        table_query = f"""
            INSERT INTO plugins(name)
            VALUES (
            ?
            );
        """
        try:
            db_conn.cursor().execute(table_query, (plugin_name, ))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Inserted new plugin into the database: {plugin_name}",
                       origin=L_DATABASE)
                log(INFO, f"Inserted new plugin into the database: {plugin_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            if 'UNIQUE' not in str(err):
                dprint(err)
            return False 

def get_all_plugin_help(db_cursor):
        # Output Format: List[str]
        get_plugin_help_query = f"""
                            SELECT plugins.name, plugins_help.help_text
                            FROM plugins, plugins_help
                            WHERE plugins.plugin_id = plugins_help.plugin_id;
                        """
        try:
            db_cursor.execute(get_plugin_help_query)
            result_dict = {}
            result_row = db_cursor.fetchall()
            if result_row is None:
                return None
            for i, item in enumerate(result_row):
                result_dict[list(result_row)[i][0]] = GetDB.get_plugin_data(db_cursor=db_cursor,
                                                                            plugin_name=list(result_row)[i][0])
            return result_dict
        except Error as err:
            dprint(err)
            return None 

def get_all_plugin_data(db_cursor):
        # Output Format: { plugin_name: [(command_name, level), (command_name, level)], plugin_name: ...}
        get_plugin_query = f"""
                    SELECT plugins.name, commands.name, commands.level
                    FROM plugins, commands, permission_levels
                    WHERE plugins.plugin_id = commands.plugin_id
                    AND commands.level = permission_levels.level_id
                """
        try:
            db_cursor.execute(get_plugin_query)
            result_dict = {}
            result_row = db_cursor.fetchall()
            if result_row is None:
                return None
            for i, item in enumerate(result_row):
                result_dict[list(result_row)[i][0]] = GetDB.get_plugin_data(db_cursor=db_cursor,
                                                                            plugin_name=list(result_row)[i][0])
            return result_dict
        except Error as err:
            dprint(err)
            return None 

def update_user_privileges(db_conn, user_name, level, ignore_file_save=False) -> bool:
        update_privileges_query = f"""
            UPDATE permissions
            SET level = ?
            WHERE user_id = (SELECT users.user_id FROM users WHERE users.name = ? LIMIT 1)
            AND ? = (SELECT permission_levels.level_id FROM permission_levels WHERE permission_levels.level_id = ?);
        """
        try:
            db_conn.cursor().execute(update_privileges_query, (level, user_name, level, level))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Updated user permission in the database: {user_name}-{level}",
                       origin=L_DATABASE)
                log(INFO, f"Updated user permission in the database: {user_name}-{level}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            dprint(err)
            return False 

def update_plugin_help(db_conn, plugin_name, plugin_help_text, ignore_file_save=False) -> bool:
        update_help_query = f"""
            UPDATE plugins_help
            SET help_text = ?
            WHERE plugin_id = (SELECT plugins.plugin_id from plugins WHERE plugins.name = ? );
        """
        try:
            db_conn.cursor().execute(update_help_query, (plugin_help_text, plugin_name))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Updated plugin help text in the database: {plugin_name}",
                       origin=L_DATABASE)
                log(INFO, f"Updated plugin help text in the database: {plugin_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            dprint(err)
            return False 

def update_alias(db_conn, alias_name, commands, ignore_file_save=False) -> bool:
        update_alias_query = f"""
            UPDATE aliases
            SET alias = ?
            WHERE name = ?;
        """
        try:
            db_conn.cursor().execute(update_alias_query, (commands, alias_name))
            save_memory_db(db_conn)
            if not ignore_file_save:
                save_memory_db_to_file()
            if db_conn.cursor().rowcount == -1:
                dprint(f"Updated alias in the database: {alias_name}",
                       origin=L_DATABASE)
                log(INFO, f"Updated alias in the database: {alias_name}",
                    origin=L_DATABASE)
                return True
            return False
        except Error as err:
            dprint(err)
            return False 

def replace_vms(self, conn, vms):
        """ Update vms from the vms table which belong to this tenant. """
        tenant_id = self.id
        vms = [(vm_id, vm_name, tenant_id) for vm_id, vm_name in vms]
        try:
            # Delete old VMs
            conn.execute(
                "DELETE FROM vms WHERE tenant_id = ?",
                [tenant_id]
            )

            conn.executemany(
                "INSERT INTO vms(vm_id, vm_name, tenant_id) VALUES (?, ?, ?)",
                vms
            )
            conn.commit()
        except sqlite3.Error as e:
            logging.error("Error %s when replace vms table with vms %s",
                          e, vms)
            return str(e)

        return None 

def remove_vms(self, conn, vms):
        """ Remove vms from the vms table for this tenant. """
        tenant_id = self.id
        vms = [(vm_id, tenant_id) for vm_id, _ in vms]
        try:
            conn.executemany(
                "DELETE FROM vms WHERE vm_id = ? AND tenant_id = ?",
                vms
            )
            conn.commit()
        except sqlite3.Error as e:
            logging.error("Error %s when removing from vms table with vms %s",
                          e, vms)
            return str(e)

        return None 

def execute_sql(self, stmt, args=None, commit=True):
        """
        Execute an SQL statement.

        Attempt to execute an SQL statement and log any errors. Return True if
        successful and false if not.
        """
        self.log.debug('Executing {0} with args {1}.'.format(stmt, args))

        try:
            if args is None:
                self.cur.execute(stmt)
            else:
                self.cur.execute(stmt, args)

            if commit is True:
                self.con.commit()

            return True

        except sqlite3.Error as e:
            self.log.debug(e)
            return False 

def add_vms(self, conn, vms):
        """ Add vms in the vms table for this tenant. """
        tenant_id = self.id
        vms = [(vm_id, vm_name, tenant_id) for vm_id, vm_name in vms]
        if vms:
            try:
                conn.executemany(
                    "INSERT INTO vms(vm_id, vm_name, tenant_id) VALUES (?, ?, ?)",
                    vms
                )
                conn.commit()
            except sqlite3.Error as e:

                logging.error("Error %s when inserting into vms table with vms %s",
                              e, vms)
                return str(e)

        return None 

def get(self, key):
        self.logger.debug("Fetching key %s", key)
        query = "SELECT value from %s WHERE key=?" % self.table
        try:
            conn = sqlite3.connect(self.dburi)
            c = conn.cursor()
            r = c.execute(query, (key,))
            value = r.fetchall()
        except sqlite3.Error:
            self.logger.exception("Error fetching key %s", key)
            raise CSStoreError('Error occurred while trying to get key')
        self.logger.debug("Fetched key %s got result: %r", key, value)
        if len(value) > 0:
            return value[0][0]
        else:
            return None 

def set(self, key, value, replace=False):
        self.logger.debug("Setting key %s to value %s (replace=%s)",
                          key, value, replace)
        if key.endswith('/'):
            raise ValueError('Invalid Key name, cannot end in "/"')
        if replace:
            query = "INSERT OR REPLACE into %s VALUES (?, ?)"
        else:
            query = "INSERT into %s VALUES (?, ?)"
        setdata = query % (self.table,)
        try:
            conn = sqlite3.connect(self.dburi)
            with conn:
                c = conn.cursor()
                self._create(c)
                c.execute(setdata, (key, value))
        except sqlite3.IntegrityError as err:
            raise CSStoreExists(str(err))
        except sqlite3.Error:
            self.logger.exception("Error storing key %s", key)
            raise CSStoreError('Error occurred while trying to store key') 

def span(self, key):
        name = key.rstrip('/')
        self.logger.debug("Creating container %s", name)
        query = "INSERT into %s VALUES (?, '')"
        setdata = query % (self.table,)
        try:
            conn = sqlite3.connect(self.dburi)
            with conn:
                c = conn.cursor()
                self._create(c)
                c.execute(setdata, (name,))
        except sqlite3.IntegrityError as err:
            raise CSStoreExists(str(err))
        except sqlite3.Error:
            self.logger.exception("Error creating key %s", name)
            raise CSStoreError('Error occurred while trying to span container') 

def load_config(yaml_file):
    logger.debug("Loading configuration from %s" % yaml_file)
    fd = None
    try:
        fd = open(yaml_file)
    except Exception as e:
        logger.error(
            "Error reading configuration file %s, ignoring..." % yaml_file
        )
        return

    try:
        return yaml.load(fd, Loader=yaml.FullLoader)
    except Exception as e:
        logger.error(
            "Error parsing configuration file %s: %s" % (yaml_file, e)
        )
        return 

def _try_load_json(self, jsonfile):
        logger.debug("Loading json configuration from %s" % jsonfile)
        fd = None
        try:
            fd = open(jsonfile)
        except Exception as e:
            logger.debug(
                "Error reading configuration file %s: %s; "
                "ignoring..." % (jsonfile, e)
            )
            return {}

        config = None
        try:
            config = json.load(fd)
        except Exception as e:
            logger.error(
                "Error parsing configuration file %s: %s" % (jsonfile, e)
            )
            return {}

        logger.trace("contents read from %s: %s" % (jsonfile, config))
        return config 

def create_vhosts_table():

    sql_create_vhosts_table = """ CREATE TABLE IF NOT EXISTS vhosts (
                                        id INTEGER PRIMARY KEY,
                                        ip text,
                                        vhost text NOT NULL,
                                        in_scope int,
                                        explicit_out_scope int,
                                        submitted int NOT NULL,                                                                                
                                        workspace text
                                    ); """

    try:
        CUR.execute(sql_create_vhosts_table)
        CONNECTION.commit()
    except Error as e:
        print(e)

#############################
# Table: Workspace
############################# 

def create_path_table():
    sql_create_paths_table = """ CREATE TABLE IF NOT EXISTS paths (
                                        id integer PRIMARY KEY,
                                        ip text NOT NULL,
                                        port int NOT NULL,
                                        path text NOT NULL UNIQUE,
                                        url_status int,
                                        submitted int,
                                        url_screenshot_filename text,                                        
                                        workspace text NOT NULL
                                    ); """

    try:
        CUR.execute(sql_create_paths_table)
        CONNECTION.commit()
    except Error as e:
        print(e) 

def create_celerystalk_table():
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """

    sql_create_celerystalk_table = """ CREATE TABLE IF NOT EXISTS celerystalk (
                                        install_path text PRIMARY KEY                                        
                                    ); """

    try:
        CUR.execute(sql_create_celerystalk_table)
        CONNECTION.commit()
    except Error as e:
        print(e) 

def create_current_workspace_table():
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """

    sql_create_current_workspace_table = """ CREATE TABLE IF NOT EXISTS current_workspace (
                                        current_db text PRIMARY KEY                                        
                                    ); """

    try:
        CUR.execute(sql_create_current_workspace_table)
        CONNECTION.commit()
    except Error as e:
        print(e) 

def create_workspace_table():
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return:
    """

    sql_create_workspace_table = """ CREATE TABLE IF NOT EXISTS workspace (
                                        name text PRIMARY KEY,
                                        output_dir text NOT NULL,
                                        mode text NOT NULL                                        
                                    ); """

    try:
        CUR.execute(sql_create_workspace_table)
        CONNECTION.commit()
    except Error as e:
        print(e) 

def load_from_location(cls, collection_location=None, filename=None,
                           default_collection=None, session=None):
        if filename is None:
            filename = collection_location.get_local_filename()

        if default_collection is None:
            default_collection = cls(session=session)

        conn = sqlite3.connect(
            filename, SQLITE_TIMEOUT, SQLITE_DETECT_TYPES,
            SQLITE_ISOLATION, False, SQLITE_FACTORY,
            SQLITE_CACHED_STATEMENTS)

        cursor = conn.cursor()
        try:
            for row in cursor.execute(
                    "select value from metadata where key='schema'"):
                result = cls.from_json(row[0], session=session)
        except sqlite3.Error:
            result = default_collection

        result.location = collection_location
        result.load_from_local_file(filename)

        return result 

def saveProxyLog(name, config):
    '''
    Save proxy log database into a new file

    :param name: Name of file to save data into (should be .db)
    :param config: Proxy ConfigParser object from proxy.ini
    :return: None
    '''

    log = config.get("DATABASE", "filename")
    oldpath = os.path.join(faradayHelper.userPath, 'lib', log)
    newpath = os.path.join(faradayHelper.userPath, 'lib', name)

    try:
        shutil.move(oldpath, newpath)
        sys.exit(0)

    except shutil.Error as e:
        logger.error(e)
    except IOError as e:
        logger.error(e) 

def saveTelemetryLog(name, config):
    '''
    Save telemetry log database into a new file

    :param name: Name of file to save data into (should be .db)
    :param config: Telemetry ConfigParser object from telmetry.ini
    :return: None
    '''

    log = config.get("DATABASE", "filename")
    oldpath = os.path.join(faradayHelper.userPath, 'lib', log)
    newpath = os.path.join(faradayHelper.userPath, 'lib', name)
    try:
        shutil.move(oldpath, newpath)
        sys.exit(0)

    except shutil.Error as e:
        logger.error(e)
    except IOError as e:
        logger.error(e)