Добавить вопросы по тематикам #55

[yebgenia]

Добавлен скрипт для создания хранимой процедуры для удобного добавления вопросов.

Добавление вопросов по разным тематикам разделены по файлам.

[RullDeef]

SQL Risks Found

db/init/02_insert_prolog_questions.sql

```\n +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS > SQL FILE NAME :: db/init/02_insert_prolog_questions.sql > COLOR MODE :: DISABLED > VERBOSE MODE :: ENABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement at line 86: call add_question( 'prolog', 'Какой тип данных используется для представления чисел с плавающей точкой в языке программирования prolog?', array[ 'real', 'double', 'float', 'В языке prolog нет вещественных чисел' ], 2 ); [db/init/02_insert_prolog_questions.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Imprecise Data Type ● Use precise data types: Virtually any use of FLOAT, REAL, or DOUBLE PRECISION data types is suspect. Most applications that use floating-point numbers don't require the range of values supported by IEEE 754 formats. The cumulative impact of inexact floating-point numbers is severe when calculating aggregates. Instead of FLOAT or its siblings, use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers. These data types store numeric values exactly, up to the precision you specify in the column definition. Do not use FLOAT if you can avoid it. [Matching Expression: float at lines 91, 93] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 0 > Medium Risk :: 1 > Low Risk :: 0 > Hints :: 0 ```

db/init/02_insert_python_questions.sql

```\n +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS > SQL FILE NAME :: db/init/02_insert_python_questions.sql > COLOR MODE :: DISABLED > VERBOSE MODE :: ENABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement at line 26: call add_question( 'python', 'int, str, float:', array[ 'Объект, переменная, цифра', 'Вещественное число, строка, целое число', 'Целое число, строка, вещественное число', 'Таких типов не существует' ], 2 ); [db/init/02_insert_python_questions.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Imprecise Data Type ● Use precise data types: Virtually any use of FLOAT, REAL, or DOUBLE PRECISION data types is suspect. Most applications that use floating-point numbers don't require the range of values supported by IEEE 754 formats. The cumulative impact of inexact floating-point numbers is severe when calculating aggregates. Instead of FLOAT or its siblings, use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers. These data types store numeric values exactly, up to the precision you specify in the column definition. Do not use FLOAT if you can avoid it. [Matching Expression: float at line 29] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 0 > Medium Risk :: 1 > Low Risk :: 0 > Hints :: 0 ```

db/init/10_create_constrants.sql

```\n +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS > SQL FILE NAME :: db/init/10_create_constrants.sql > COLOR MODE :: DISABLED > VERBOSE MODE :: ENABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement at line 1: \c quizdb alter table users add constraint c_users_pk primary key (id), add constraint c_users_role check (("role") in ('user', 'admin')); [db/init/10_create_constrants.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Values In Definition ● Don't specify values in column definition: With enum, you declare the values as strings, but internally the column is stored as the ordinal number of the string in the enumerated list. The storage is therefore compact, but when you sort a query by this column, the result is ordered by the ordinal value, not alphabetically by the string value. You may not expect this behavior. There's no syntax to add or remove a value from an ENUM or check constraint; you can only redefine the column with a new set of values. Moreover, if you make a value obsolete, you could upset historical data. As a matter of policy, changing metadata — that is, changing the definition of tables and columns—should be infrequent and with attention to testing and quality assurance. There's a better solution to restrict values in a column: create a lookup table with one row for each value you allow. Then declare a foreign key constraint on the old table referencing the new table. Use metadata when validating against a fixed set of values. Use data when validating against a fluid set of values. [Matching Expression: in ( at line 7] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 0 > Medium Risk :: 1 > Low Risk :: 0 > Hints :: 0 ```

[RullDeef]

SQL Risks Found

db/init/02_insert_prolog_questions.sql

```\n +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS > SQL FILE NAME :: db/init/02_insert_prolog_questions.sql > COLOR MODE :: DISABLED > VERBOSE MODE :: ENABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement at line 86: call add_question( 'prolog', 'Какой тип данных используется для представления чисел с плавающей точкой в языке программирования prolog?', array[ 'real', 'double', 'float', 'В языке prolog нет вещественных чисел' ], 2 ); [db/init/02_insert_prolog_questions.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Imprecise Data Type ● Use precise data types: Virtually any use of FLOAT, REAL, or DOUBLE PRECISION data types is suspect. Most applications that use floating-point numbers don't require the range of values supported by IEEE 754 formats. The cumulative impact of inexact floating-point numbers is severe when calculating aggregates. Instead of FLOAT or its siblings, use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers. These data types store numeric values exactly, up to the precision you specify in the column definition. Do not use FLOAT if you can avoid it. [Matching Expression: float at lines 91, 93] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 0 > Medium Risk :: 1 > Low Risk :: 0 > Hints :: 0 ```

db/init/02_insert_python_questions.sql

```\n +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS > SQL FILE NAME :: db/init/02_insert_python_questions.sql > COLOR MODE :: DISABLED > VERBOSE MODE :: ENABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement at line 26: call add_question( 'python', 'int, str, float:', array[ 'Объект, переменная, цифра', 'Вещественное число, строка, целое число', 'Целое число, строка, вещественное число', 'Таких типов не существует' ], 2 ); [db/init/02_insert_python_questions.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Imprecise Data Type ● Use precise data types: Virtually any use of FLOAT, REAL, or DOUBLE PRECISION data types is suspect. Most applications that use floating-point numbers don't require the range of values supported by IEEE 754 formats. The cumulative impact of inexact floating-point numbers is severe when calculating aggregates. Instead of FLOAT or its siblings, use the NUMERIC or DECIMAL SQL data types for fixed-precision fractional numbers. These data types store numeric values exactly, up to the precision you specify in the column definition. Do not use FLOAT if you can avoid it. [Matching Expression: float at line 29] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 0 > Medium Risk :: 1 > Low Risk :: 0 > Hints :: 0 ```

db/init/10_create_constrants.sql

```\n +-------------------------------------------------+ | SQLCHECK | +-------------------------------------------------+ > RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS > SQL FILE NAME :: db/init/10_create_constrants.sql > COLOR MODE :: DISABLED > VERBOSE MODE :: ENABLED > DELIMITER :: ; ------------------------------------------------- ==================== Results =================== ------------------------------------------------- SQL Statement at line 1: \c quizdb alter table users add constraint c_users_pk primary key (id), add constraint c_users_role check (("role") in ('user', 'admin')); [db/init/10_create_constrants.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Values In Definition ● Don't specify values in column definition: With enum, you declare the values as strings, but internally the column is stored as the ordinal number of the string in the enumerated list. The storage is therefore compact, but when you sort a query by this column, the result is ordered by the ordinal value, not alphabetically by the string value. You may not expect this behavior. There's no syntax to add or remove a value from an ENUM or check constraint; you can only redefine the column with a new set of values. Moreover, if you make a value obsolete, you could upset historical data. As a matter of policy, changing metadata — that is, changing the definition of tables and columns—should be infrequent and with attention to testing and quality assurance. There's a better solution to restrict values in a column: create a lookup table with one row for each value you allow. Then declare a foreign key constraint on the old table referencing the new table. Use metadata when validating against a fixed set of values. Use data when validating against a fluid set of values. [Matching Expression: in ( at line 7] ==================== Summary =================== All Anti-Patterns and Hints :: 1 > High Risk :: 0 > Medium Risk :: 1 > Low Risk :: 0 > Hints :: 0 ```