Esempio n. 1
0
    def check_passwd_for_svcdb(user_id, password):
        package_name = 'pkg_user_auth'
        if StrUtil.get_safe_config(current_app,
                                   'PROJECT_STAGE') == Const.DEVELOPMENT:
            package_name = 'pkg_user_auth_debug'

        current_sqlalchemy_echo = StrUtil.get_safe_config(
            current_app, 'SQLALCHEMY_ECHO')

        # 認証SQLのログを出力しないようにする
        db.session.bind.echo = False

        returnVal = db.session.execute(
            'select ' + package_name +
            '.check_passwd_for_svcdb(:user_id, :password) as val from dual', {
                'user_id': user_id,
                'password': password
            }).fetchone().val

        # 現状設定に戻す
        db.session.bind.echo = current_sqlalchemy_echo

        if returnVal == 0:
            return True
        return False
Esempio n. 2
0
def set_cookie(session_cookie_name, tuid, redirectUrl):
    random_str = '{0}{1}'.format(StrUtil.make_random_str(25),
                                 str(CreateSeq.getSessionIdSeq()).zfill(9))

    StrUtil.print_debug('##########random_str:' + random_str)
    cst = SvcdbSessionTable(session_cookie_name, random_str, tuid)
    db.session.add(cst)
    db.session.commit()

    if request.method == 'GET':
        next_url = request.args.get('next_url')
    else:
        next_url = request.form['next_url']

    if not next_url:
        next_url = url_for(redirectUrl)
    else:
        next_url = urllib.parse.unquote(next_url)

    StrUtil.print_debug('next_url:' + next_url)
    response = make_response(redirect(next_url))
    response.set_cookie(session_cookie_name, random_str)
    response.set_cookie("session_id",
                        random_str,
                        path=cookie_path(tuid, random_str))
    return response
Esempio n. 3
0
 def _check_date(year, month, day):
     try:
         newDataStr = "%04d/%02d/%02d" % (int(year), int(month), int(day))
         newDate = datetime.datetime.strptime(newDataStr, "%Y/%m/%d")
         return True
     except Exception as e:
         tb = sys.exc_info()[2]
         StrUtil.print_error("##########_check_date error_msg:{}".format(
             str(e.with_traceback(tb))))
         return False
Esempio n. 4
0
    def _get_ymd(date_str, fmt, date_hash):
        if fmt == 'YYYY-MM-DD' or fmt == 'YYYY/MM/DD':
            match = re.search('^(\d+)[\-\/](\d+)[\-\/](\d+)$', date_str)
            if not match:
                return 1
            date_hash['yyyy'] = match.group(1)
            date_hash['mm'] = match.group(2)
            date_hash['dd'] = match.group(3)
        elif fmt == 'YY/MM/DD':
            match = re.search('^(\d{1,2})[\-\/](\d{1,2})[\-\/](\d{1,2})$',
                              date_str)
            if not match:
                return 1
            if int(match.group(3)) > 50:
                date_hash['yyyy'] = 1900 + int(match.group(1))
            else:
                date_hash['yyyy'] = 2000 + int(match.group(1))
            date_hash['mm'] = match.group(2)
            date_hash['dd'] = match.group(3)
        elif fmt == 'DD/Mon/YY' or fmt == 'DD-Mon-YY':
            match = re.search('^(\d+)[\-\/](\w+)[\-\/](\d+)$', date_str)
            if not match:
                return 1
            if int(match.group(3)) > 50:
                date_hash['yyyy'] = 1900 + int(match.group(3))
            else:
                date_hash['yyyy'] = 2000 + int(match.group(3))
            date_hash['mm'] = ArrUtil.search_array(DateUtil.MoYs,
                                                   match.group(2)) + 1
            date_hash['dd'] = match.group(1)

            if int(date_hash['mm']) <= 0:
                return 1
        elif fmt == 'DD/Mon/YYYY' or fmt == 'DD-Mon-YYYY':
            match = re.search('^(\d+)[\-\/](\w+)[\-\/](\d+)$', date_str)
            if not match:
                return 1
            date_hash['yyyy'] = int(match.group(3))
            date_hash['mm'] = ArrUtil.search_array(DateUtil.MoYs,
                                                   match.group(2)) + 1
            date_hash['dd'] = match.group(1)

            if int(date_hash['mm']) <= 0:
                return 1
        else:
            StrUtil.print_debug("Invalid date format({})".format(fmt))
            sys.exit(1)

        return 0
Esempio n. 5
0
    def unzip_file(uf, unzipDirPath, file_name):
        df = os.path.join(unzipDirPath, str(file_name))
        try:
            decompressedFile = gzip.open(uf, 'rb')
            if not os.path.isdir(unzipDirPath):
                os.makedirs(unzipDirPath)

            openDf = open(df, 'wb')
            openDf.write(decompressedFile.read())
            decompressedFile.close()
            openDf.close()
            return df
        except Exception as e:
            StrUtil.print_error("##########unzip_file file_path:" + df)
            return None
Esempio n. 6
0
def decompress_file(params):
    params['df'] = None
    if params['disp_mode'] == 'edit':
        print(params['disp_mode'])
        svcdbFileWkE = SvcdbFileWk()
        attacheFile = svcdbFileWkE.getFile(params['edit_id'],
                                           params['file_id'])
    else:
        svcdbFileE = SvcdbFile()
        attacheFile = svcdbFileE.getFile(params['file_id'])
    if attacheFile is not None:
        try:
            file_path = os.path.join(attacheFile.dir_name,
                                     attacheFile.c_file_name)
            unzip_dir_path = str(
                StrUtil.get_safe_config(current_app, 'DOWNLOAD_DIR_PATH'))
            unzip_file_path = FileUtil.unzip_file(file_path, unzip_dir_path,
                                                  attacheFile.file_id)

            params['attacheFile'] = attacheFile
            params['df'] = unzip_file_path
        except FileNotFoundError:
            print("FileNotFoundError")

    return params
    def get_adm_session_info(session_id):
        current_time = datetime.now()

        return SvcdbSessionTable.query.filter_by(cookie_name=StrUtil.get_safe_config(current_app, 'SVCDB_SYS_COOKIE'),
                                               session_id=session_id) \
            .filter(SvcdbSessionTable.login_date >= current_time - timedelta(days=7)) \
            .filter(SvcdbSessionTable.login_date <= current_time).first()
Esempio n. 8
0
 def saveOperationLog(self,
                      user_id,
                      db_id,
                      operation_cd,
                      object_id='',
                      object_type='',
                      note=''):
     return
     ip_addr = StrUtil.get_ip_addr()
     db.session.execute(
         'begin ' + self.package_name + '.save_operation_log' +
         '(:user_id, :operation_cd, :object_id, :object_type, :db_id, :note, :ip_addr); '
         + 'end;', {
             'user_id': user_id,
             'operation_cd': operation_cd,
             'object_id': object_id,
             'object_type': object_type,
             'db_id': db_id,
             'note': note,
             'ip_addr': ip_addr
         })
Esempio n. 9
0
        def wrapper(*args, **kwargs):
            logout_user()
            StrUtil.print_debug('adm_login_required. func=[' + func.__name__ +
                                ']')

            session_id = flaskr.lib.svcdb_lib.session.get_session_id(
                StrUtil.get_safe_config(current_app, 'SVCDB_SYS_COOKIE'))
            if session_id:
                StrUtil.print_debug(
                    'login_required. session_cookie_name:{0}  session_id:{1}'.
                    format('ADMIN_SESSION_COOKIE', session_id))

                cst = SvcdbSessionTable.get_adm_session_info(session_id)
                if cst is None:
                    flash('invalid user_id or password')
                    return redirect(url_for('adm_login'))

                # 取得したユーザIDでユーザ情報を取得する
                user = User.query.filter_by(tuid=cst.user_id).first()
                if user is None:
                    flash('invalid user_id or password')
                    return redirect(url_for('adm_login'))

                # 管理者権限チェック
                pkgSvcdbSecurity = PkgSvcdbSecurity()
                if not pkgSvcdbSecurity.isAdminUser(user.tuid):
                    flash('利用権限がありません')
                    return redirect(
                        UserAuth._get_redirect_url(url_for('adm_login')))

                login_user(user, False)
            else:
                StrUtil.print_debug('login_required. no session id got.')
                return redirect(
                    UserAuth._get_redirect_url(url_for('adm_login')))

            return func(*args, **kwargs)
Esempio n. 10
0
 def sqlExcuter(sqlstr: str, *args, **kwargs):
     sqlstr = sqlstr.format(*args, **kwargs)
     StrUtil.print_debug(sqlstr)
     return db.session.execute(text(sqlstr))
Esempio n. 11
0
    def check_input_form_data_by_db2(param_prop):
        err_msgs = []
        try:
            if 'form' not in param_prop \
                    or 'table_name' not in param_prop \
                    or 'col_prop' not in param_prop:
                err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
                param_prop['err_msgs'] = err_msgs
                return

            col_prop = param_prop['col_prop']
            if 'cname' not in col_prop \
                    or 'input_field' not in col_prop \
                    or 'db_field' not in col_prop:
                err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
                param_prop['err_msgs'] = err_msgs
                return

            form = param_prop['form']
            user_tab_columns = DbUtil.get_user_tab_columns_hash(
                param_prop['table_name'])

            for idx in range(0, len(col_prop['cname'])):

                input_field = col_prop['input_field'][idx]
                value = str(form.__dict__[input_field].data)
                db_field = col_prop['db_field'][idx]

                # 必須チェック
                if 'nullable' in user_tab_columns[db_field]:
                    if user_tab_columns[db_field][
                            'nullable'] == 'N' and not value:
                        err_msgs.append(
                            Const.REQUIRED_MSG.format(col_prop['cname'][idx]))

                # 桁数チェック
                if 'data_type' in user_tab_columns[
                        db_field] and 'data_length' in user_tab_columns[
                            db_field]:
                    data_type = user_tab_columns[db_field]['data_type']
                    data_length = user_tab_columns[db_field]['data_length']
                    if data_type == 'VARCHAR2' or data_type == 'CHAR':
                        if StrUtil.lenb(value) > int(data_length):
                            err_msgs.append(
                                Const.LENGTH_OVER_MSG.format(
                                    col_prop['cname'][idx], str(data_length)))
                    elif data_type == 'NUMBER':
                        if re.search(',', str(data_length)):
                            t = value
                            t = re.sub(r'[^\.]', r'', t)
                            if len(t) > 1 or re.search('[^0-9^\.]', value):
                                err_msgs.append(
                                    Const.NUMERICAL_VALUE_REQUIRED_MSG.format(
                                        col_prop['cname'][idx]))
                        else:
                            if re.search('[^0-9]', value):
                                err_msgs.append(
                                    Const.INTEGER_VALUE_REQUIRED_MSG.format(
                                        col_prop['cname'][idx]))
                    elif data_type == 'DATE':
                        if DateUtil.check_date_format(value,
                                                      Const.DATE_FORMAT) != 0:
                            err_msgs.append(
                                Const.AVAILABLE_DATE_REQUIRED_MSG.format(
                                    col_prop['cname'][idx], value))

            param_prop['err_msgs'] = err_msgs

        except Exception as e:
            tb = sys.exc_info()[2]
            param_prop['err_msgs'] = str(e.with_traceback(tb))
            StrUtil.print_error(
                '##########check_input_form_data_by_db error_msg:{}'.format(
                    str(e.with_traceback(tb))))
Esempio n. 12
0
    def check_input_form_data_by_prop(param_prop):
        err_msgs = []
        try:
            if 'form' not in param_prop:
                err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
                param_prop['err_msgs'].extend(err_msgs)
                return

            form = param_prop['form']
            for pro in param_prop['pro_list']:
                property_type = pro.get("property_type")
                if "KEYWORD" == property_type:
                    continue

                col_name = pro.get("db_column_name").lower()
                value = form.__dict__[col_name].data
                if col_name.startswith("num_"):
                    if len(value) > 0:
                        value = int(value)
                    else:
                        value = ''

                # 必須チェック
                if pro.get("nullable") == 'FALSE' and not value:
                    err_msgs.append(
                        Const.REQUIRED_MSG.format(pro.get("property_name")))
                    continue

                if not value:
                    continue

                # 数字チェック
                if "NUMBER" == property_type:
                    if NumUtil.is_number_data(value) != 1:
                        err_msgs.append(
                            Const.NUMERICAL_VALUE_REQUIRED_MSG.format(
                                pro.get("property_name")))
                    else:
                        num_prop = {'sign_ref': '', 'i_ref': '', 'f_ref': ''}
                        NumUtil.split_number(value, num_prop)
                        if (len(num_prop['i_ref']) +
                                len(num_prop['f_ref'])) > int(
                                    pro.get("i_len")):
                            err_msgs.append(
                                Const.INTEGRAL_PART_OUT_OF_RANGE_MSG.format(
                                    pro.get("property_name"),
                                    str(pro.get("i_len") - pro.get("f_len"))))
                        if len(num_prop['f_ref']) > int(pro.get("f_len")):
                            err_msgs.append(
                                Const.FRACTIONAL_PART_OUT_OF_RANGE_MSG.format(
                                    pro.get("property_name"),
                                    str(pro.get("f_len"))))

                # 日付チェック
                elif 'DATE' == property_type:
                    if DateUtil.check_date_format(value,
                                                  Const.DATE_FORMAT) != 0:
                        err_msgs.append(
                            Const.AVAILABLE_DATE_REQUIRED_MSG.format(
                                pro.get("property_name"), value))

                # 文字列チェック
                elif 'TEXT' == property_type or 'TEXT_MULTILINE' == property_type:
                    # 桁数チェック
                    if pro.get("data_size"):
                        if StrUtil.lenb(value) > int(pro.get("data_size")):
                            err_msgs.append(
                                Const.LENGTH_OVER_MSG.format(
                                    pro.get("property_name"),
                                    str(pro.get("data_size"))))

                # バリデータチェック(正式表現)
                re_cond = pro.get('validate_rule')
                if re_cond and len(value) > 0:
                    try:
                        if not re.search(re_cond, value):
                            err_msgs.append(
                                pro.get('validate_err_msg').replace(
                                    '<#DATA#>', value))
                    except Exception as e:
                        tb = sys.exc_info()[2]
                        StrUtil.print_error(
                            '##########check_input_form_data_by_prop validate_rule:{} error_msg:{}'
                            .format(re_cond, str(e.with_traceback(tb))))
            param_prop['err_msgs'].extend(err_msgs)

        except Exception as e:
            tb = sys.exc_info()[2]
            param_prop['err_msgs'].extend(str(e.with_traceback(tb)))
            StrUtil.print_error(
                '##########check_input_form_data_by_prop error_msg:{}'.format(
                    str(e.with_traceback(tb))))
Esempio n. 13
0
    def check_input_form_data_by_db(param_prop):
        err_msgs = []
        try:
            if 'table_name' not in param_prop \
                    or 'col_prop' not in param_prop:
                err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
                param_prop['err_msgs'].extend(err_msgs)
                return

            col_prop = param_prop['col_prop']
            if 'cname' not in col_prop \
                    or 'input_value' not in col_prop \
                    or 'db_field' not in col_prop:
                err_msgs.append(Const.INVALID_PARAM_ERR_MSG)
                param_prop['err_msgs'].extend(err_msgs)
                return

            user_tab_columns = DbUtil.get_user_tab_columns_hash(
                param_prop['table_name'])

            for idx in range(0, len(col_prop['cname'])):
                value = col_prop['input_value'][idx]
                db_field = col_prop['db_field'][idx]

                # 必須チェック
                if 'nullable' in user_tab_columns[db_field]:
                    if user_tab_columns[db_field][
                            'nullable'] == 'N' and not value:
                        err_msgs.append(
                            Const.REQUIRED_MSG.format(col_prop['cname'][idx]))
                        continue

                if not value:
                    continue

                if 'data_type' in user_tab_columns[
                        db_field] and 'data_length' in user_tab_columns[
                            db_field]:
                    data_type = user_tab_columns[db_field]['data_type']
                    data_length = user_tab_columns[db_field]['data_length']

                    # 文字列チェック
                    if data_type == 'VARCHAR2' or data_type == 'CHAR':
                        # 桁数チェック
                        if StrUtil.lenb(value) > int(data_length):
                            err_msgs.append(
                                Const.LENGTH_OVER_MSG.format(
                                    col_prop['cname'][idx], str(data_length)))

                    # 数字チェック
                    elif data_type == 'NUMBER':
                        """
                        if re.search(',', str(data_length)):
                            t = value
                            t = re.sub(r'[^\.]', r'', t)
                            if len(t) > 1 or re.search('[^0-9^\.]', value):
                                err_msgs.append(
                                    Const.NUMERICAL_VALUE_REQUIRED_MSG.format(col_prop['cname'][idx]))
                        else:
                            if re.search('[^0-9]', value):
                                err_msgs.append(
                                    Const.INTEGER_VALUE_REQUIRED_MSG.format(col_prop['cname'][idx]))
                        """
                        if NumUtil.is_number_data(value) != 1:
                            err_msgs.append(
                                Const.NUMERICAL_VALUE_REQUIRED_MSG.format(
                                    col_prop['cname'][idx]))
                        else:
                            num_prop = {
                                'sign_ref': '',
                                'i_ref': '',
                                'f_ref': ''
                            }
                            NumUtil.split_number(value, num_prop)
                            if 'data_precision' in user_tab_columns[db_field] \
                                    and user_tab_columns[db_field]['data_precision'] is not None:
                                if len(num_prop['i_ref']) > int(
                                        user_tab_columns[db_field]
                                    ['data_precision']):
                                    err_msgs.append(
                                        Const.INTEGRAL_PART_OUT_OF_RANGE_MSG.
                                        format(
                                            col_prop['cname'][idx],
                                            str(user_tab_columns[db_field]
                                                ['data_precision'])))
                            if 'data_scale' in user_tab_columns[db_field] \
                                    and user_tab_columns[db_field]['data_scale'] is not None:
                                if len(num_prop['f_ref']) > int(
                                        user_tab_columns[db_field]
                                    ['data_scale']):
                                    err_msgs.append(
                                        Const.FRACTIONAL_PART_OUT_OF_RANGE_MSG.
                                        format(
                                            col_prop['cname'][idx],
                                            str(user_tab_columns[db_field]
                                                ['data_scale'])))

                    # 日付チェック
                    elif data_type == 'DATE':
                        if DateUtil.check_date_format(value,
                                                      Const.DATE_FORMAT) != 0:
                            err_msgs.append(
                                Const.AVAILABLE_DATE_REQUIRED_MSG.format(
                                    col_prop['cname'][idx], value))

                    # 文字列「CLOB」チェック
                    elif data_type == 'CLOB':
                        if len(value) > 10 * 1024:
                            err_msgs.append(
                                Const.LENGTH_OVER_MSG.format(
                                    col_prop['cname'][idx], '10,000'))

            param_prop['err_msgs'].extend(err_msgs)

        except Exception as e:
            tb = sys.exc_info()[2]
            param_prop['err_msgs'].extend(str(e.with_traceback(tb)))
            StrUtil.print_error(
                '##########check_input_form_data_by_db error_msg:{}'.format(
                    str(e.with_traceback(tb))))
Esempio n. 14
0
from flask_login import current_user

from flaskr import create_app
from flaskr.lib.conf.config import Config
from flaskr.lib.conf.const import Const
from flaskr.lib.svcdb_lib.str_util import StrUtil

app = create_app()


@app.context_processor
def svcdb_processor():
    resp_dict = {
        "system_name": Const.SYSTEM_NAME,
        "current_user": current_user,
        "user_name":
        current_user.get_user_name() if current_user.is_active else "",
        "appVer": Config.APP_VER
    }
    return resp_dict


if __name__ == '__main__':
    app.run(debug=StrUtil.get_safe_config(app, 'DEBUG'))
Esempio n. 15
0
        def wrapper(*args, **kwargs):
            logout_user()
            StrUtil.print_debug('login_required. func=[' + func.__name__ + ']')
            """
            db_id = flaskr.lib.svcdb_lib.session.get_db_id()
            if not db_id:
                flash('[db_id]パラメータが必要です')
                return redirect(url_for('login'))

            # データベースオブジェクトを取得する
            current_db = flaskr.lib.svcdb_lib.session.get_current_db(db_id)

            # グローバル変数に設定する
            flaskr.lib.svcdb_lib.session.current_db = current_db

            if not current_db:
                flash('[db_id:{}]情報を取得できません'.format(db_id))
                return redirect(url_for('login', db_id=db_id))
            StrUtil.print_debug('login_required. cur_db.db_id=[' + str(current_db.db_id) + ']')

            # アクセス権限チェック
            pkgIpAddrUtil = PkgIpAddrUtil()
            id_addr = StrUtil.get_ip_addr()
            if not id_addr or not pkgIpAddrUtil.isDbIpAddrVisible(db_id, id_addr):
                flash('利用権限がありません')
                return redirect(url_for('login', db_id=db_id))
            session_id = flaskr.lib.svcdb_lib.session.get_session_id(current_db.session_cookie_name)
            """
            session_id = flaskr.lib.svcdb_lib.session.get_session_id(
                Const.SESSION_COOKIE_NAME)
            if session_id:
                StrUtil.print_debug(
                    'login_required. session_cookie_name:{0}  session_id:{1}'.
                    format(Const.SESSION_COOKIE_NAME, session_id))

                # セッションテーブルからユーザIDを取得する(有効期限:一週間)
                cst = SvcdbSessionTable.get_session_info(
                    Const.SESSION_COOKIE_NAME, session_id)
                if cst is None:
                    flash('invalid user_id or password')
                    return redirect(url_for('login'))

                # 取得したユーザIDでユーザ情報を取得する
                user = User.query.filter_by(tuid=cst.user_id).first()
                if user is None:
                    flash('invalid user_id or password')
                    return redirect(url_for('login'))
                """
                # 参照権限チェック
                pkgSvcdbSecurity = PkgSvcdbSecurity()
                if not pkgSvcdbSecurity.isDbVisible(db_id, user.tuid):
                    flash('このDBを参照する権限がありません')
                    return redirect(url_for('login', db_id=db_id))
                """

                StrUtil.print_debug('login_required. user_id=[' +
                                    str(cst.user_id) + ']')
                login_user(user, False)
            else:
                StrUtil.print_debug('login_required. no session id got.')
                return redirect(UserAuth._get_redirect_url(url_for('login')))

            return func(*args, **kwargs)
Esempio n. 16
0
# coding:utf-8
import os
import sys

from flaskr.lib.svcdb_lib.date_util import DateUtil
from flaskr.lib.svcdb_lib.num_util import NumUtil
from flaskr.lib.svcdb_lib.str_util import StrUtil

sys.path.append('/home04/svcdb/flask/svcdb/')
os.environ['NLS_LANG'] = 'JAPANESE_JAPAN.AL32UTF8'

from flaskr import create_app

app = create_app()
app.app_context().push()

# 日付チェック
rst = DateUtil.check_date_format('2020/02/18', 'YYYY/MM/DD')
app.logger.debug(rst)

# 日付チェック
rst = NumUtil.is_number_data('aa')
app.logger.debug(rst)
rst = NumUtil.is_integer_data('10.22')
app.logger.debug(rst)
num_prop = {'sign_ref': '', 'i_ref': '', 'f_ref': ''}
rst = NumUtil.split_number('10.22', num_prop)
app.logger.debug(rst)

rst = StrUtil.truncate('ああああああああああああああああああああああああああああああああああああああああああ', 20)
app.logger.debug(rst)
Esempio n. 17
0
 def get_max_upload_file_size():
     max_upload_file_size = StrUtil.get_safe_config(
         current_app, 'MAX_UPLOAD_FILE_SIZE_MB')
     if not max_upload_file_size or max_upload_file_size <= 0:
         max_upload_file_size = 40
     return max_upload_file_size