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
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
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
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
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
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()
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
})
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)
def sqlExcuter(sqlstr: str, *args, **kwargs):
sqlstr = sqlstr.format(*args, **kwargs)
StrUtil.print_debug(sqlstr)
return db.session.execute(text(sqlstr))
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))))
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))))
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))))
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'))
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)
# 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)
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