Structured Query Language Code Research Articles

Uso de Técnicas de Minería de Datos para la Detección de Ataques de Inyección de SQL en Sistemas de Bases de Datos

In any business organization, database infrastructures are subject to various structured query language (SQL) injection attacks, such as tautologies, alternative coding, stored procedures, use of the union operator, piggyback, among others. This article describes a data mining project developed to mitigate the problem of identifying SQL injection attacks on databases. The project was conducted using an adaptation of the cross-industry standard process for data mining (CRISP-DM) methodology. A total of 12 python libraries was used for cleaning, transformation, and modeling. The anomaly detection model was carried out using clustering by the k – nearest neighbors (kNN) algorithm. The query text was analyzed for the groups with anomalies to identify sentences presenting attack traces. A web interface was implemented to display the daily summary of the attacks found. The information source was obtained from the transactions log of a PostgreSQL database server. Our results allowed the identification of different attacks by injection of SQL code above 80%. The execution time for processing half a million transaction log was approximately 60 minutes using a computer with the following characteristics: Intel® Core i7 processor 7th generation, 12GB RAM and 500GB SSD.

Studying the characteristics of SQL-related development tasks: An empirical study

A key function of a software system is its ability to facilitate the manipulation of data, which is often implemented using a flavour of the Structured Query Language (SQL). To develop the data operations of software (i.e, creating, retrieving, updating, and deleting data), developers are required to excel in writing and combining both SQL and application code. The problem is that writing SQL code in itself is already challenging (e.g., SQL anti-patterns are commonplace) and combining SQL with application code (i.e., for SQL development tasks) is even more demanding. Meanwhile, we have little empirical understanding regarding the characteristics of SQL development tasks. Do SQL development tasks typically need more code changes? Do they typically have a longer time-to-completion? Answers to such questions would prepare the community for the potential challenges associated with such tasks. Our results obtained from 20 Apache projects reveal that SQL development tasks have a significantly longer time-to-completion than SQL-unrelated tasks and require significantly more code changes. Through our qualitative analyses, we observe that SQL development tasks require more spread out changes, effort in reviews and documentation. Our results also corroborate previous research highlighting the prevalence of SQL anti-patterns. The software engineering community should make provision for the peculiarities of SQL coding, in the delivery of safe and secure interactive software.

SQL#: A Language for Maintainable and Debuggable Database Queries

Structured Query Language (SQL) is the dominant language for managing relational databases. However, complex SQL queries are hard to write and maintain because of the intricate inter-table and inter-column relations. To this end, we propose a novel query language called SQL#, which allows programmers to construct complex queries module by module and explicitly specify the relations between different modules according to the logical steps of constructing queries. Besides, we design a SQL#-based system, aiming to facilitate the maintenance of SQL# queries. Specifically, the system renders a SQL# program into a hierarchical graph, which could help programmers understand the high-level structures of SQL# programs and the intricate relations between different components within SQL# programs. In addition, the system can ease the generation of the intermediate tables that correspond to the logical steps of constructing queries, which could help programmers debug complex SQL# queries. Notably, the design of SQL# makes it easy for the system to generate the hierarchical graph and the intermediate tables. Controlled experiments suggest that the SQL#-based system reduces the durations of writing and understanding database queries by 79% and 39%, respectively, compared to raw SQL code.

Developing Sentinel Surveillance for Chlamydia and Gonorrhea Using Test Results From Routine Screening During Pregnancy.

Background Interpretation of case-based surveillance of chlamydia and gonorrhea is limited by the lack of negative tests for comparison. We sought to develop a sustainable electronic health record (EHR)-based approach to disease surveillance in a sentinel population of pregnant persons. Methods We conducted a 1-year assessment of sexually transmitted infections (STIs) in persons receiving at least 1 pregnancy-related visit within our university medical center. Data were obtained using EHR analytic structured query language code. Patients were categorized by whether they had an STI test during pregnancy and if screened, by the STI test results (positive or negative). We assessed screening and positivity by demographic using bivariate analyses. Predictors of a positive STI test were determined using logistic regression. Results We identified 4553 persons who received pregnancy care from January 1 to December 31, 2021. Seventy-six percent (n = 3483) of persons were screened for an STI during pregnancy. Those who identified as white or had private insurance were less likely to have a chlamydia test. Among persons screened, Trichomonas was the most commonly detected STI (5%, 141/2698) followed by chlamydia (4%, 135/3456), and gonorrhea (0.7% 24/3468). Predictors of a positive STI test during pregnancy were Black race (adjusted odds ratio [aOR], 6.0; 95% confidence interval [95% CI], 4.2–8.7), 25 years or younger (aOR, 2.5; 95% CI, 1.9–3.3), and public insurance (aOR, 1.6; 95% CI, 1.2–2.1). Conclusions We demonstrated that EHRs can be used to assess gonorrhea and chlamydia positivity. These methods could potentially be applied in other jurisdictions to improve the understanding of national STI surveillance.

The Robson classification for caesarean section-A proposed method based on routinely collected health data.

BackgroundWith an increasing rate of caesarean sections as well as rising numbers of multiple pregnancies, valid classifications for benchmarking are needed. The Robson classification provides a method to group cases with caesarean section in order to assess differences in outcome across regions and sites. In this study we set up a novel method of classification by using routinely collected health data. We hypothesize i that routinely collected health data can be used to apply complex medical classifications and ii that the Robson classification is capable of classifying mothers and their corresponding newborn into meaningful groups with regard to outcome.Methods and findingsThe study was conducted at the coding department and the department of obstetrics and gynecology Inselspital, University Hospital of Bern, Switzerland. The study population contained inpatient cases from 2014 until 2017. Administrative and health data were extracted from the Data Warehouse. Cases were classified by a Structured Query Language code according to the Robson criteria using data from the administrative system, the electronic health record and from the laboratory system. An automated query to classify the cases according to Robson could be implemented and successfully validated. A linkage of the mother’s class to the corresponding newborn could be established. The distribution of clinical indicators was described. It could be shown that the Robson classes are associated to outcome parameters and case related costs.ConclusionsWith this study it could be demonstrated, that a complex query on routinely collected health data would serve for medical classification and monitoring of quality and outcome. Risk-stratification might be conducted using this data set and should be the next step in order to evaluate the Robson criteria and outcome. This study will enhance the discussion to adopt an automated classification on routinely collected health data for quality assurance purposes.

Development and Validation of an Algorithm to Classify as Equivalent the Procedures in ICD-10-PCS That Differ Only by Laterality.

The switch from International Classification of Diseases, Ninth Revision, Clinical Modification to International Classification of Diseases, Tenth Revision, Procedure Coding System (ICD-10-PCS) for coding of inpatient procedures in the United States increased the number of procedural codes more than 19-fold, in large part due to the addition of laterality. We examined ICD-10-PCS codes for pairs of mirror-image procedures that are surgically equivalent. We developed an algorithm in structured query language (SQL) to identify ICD-10-PCS codes differing only by laterality. We quantified the impact of laterality on the number of commonly performed major therapeutic procedures (ie, surgical diversity) using 2 quarters of discharge abstracts from Texas. Of the 75,789 ICD-10-PCS codes from federal fiscal year 2017, 16,839 (22.3%) pairs differed only by laterality (with each pair contributing 2 codes). With the combining of equivalent codes, diversity in the state of Texas decreased from 78.2 to 74.1 operative procedures (95% confidence interval, 5.1 to -3.1; P < .001). Our algorithm identifies ICD-10-PCS codes that differ only by laterality. However, laterality had a small effect on surgical diversity among major therapeutic procedures. Our SQL code and the lookup table will be useful for all US inpatient analyses of ICD-10-PCS surgical data, because combining procedures differing only by laterality will often be desired.

Concepts and evaluation of the extended entity-relationship approach to database design in a multi-paradigm information system modeling tool

Different approaches to information system (IS) development are based on different data models. The selection of a data model for conceptual design, among other things, depends on the problem domain, the knowledge, and the personal preferences of an IS designer. In some situations, a simultaneous usage of different approaches to the conceptual database design and IS development may lead to the most appropriate solutions. In our previous research we have developed a tool that provides an evolutive and incremental approach to IS development, which is based on the form type data model. The approaches based on the Extended Entity-Relationship (EER) and class data models are broadly accepted throughout the community of IS designers. In order to support the simultaneous usage of approaches based on the form type, EER and class data models, we have developed the Multi-Paradigm Information System Modeling Tool (MIST). In this paper, we present a part of our MIST tool that supports EER approach to a database design. MIST components currently provide a formal specification of an EER database schema specification and its transformation into the relational data model, or the class model. Also, MIST allows generation of Structured Query Language code for a database creation and procedural code for implementing database constraints. In addition, Java code that stores and processes data from the database, may be generated from the class model. In this paper, we present the evaluation study of the MIST EER domain-specific language. Users' perceptions of language quality characteristics are used for the evaluation.

Are models easier to understand than code? An empirical study on comprehension of entity-relationship (ER) models vs. structured query language (SQL) code

Models in Software Engineering are considered as abstract representations of software systems. Models highlight relevant details for a certain purpose, whereas irrelevant ones are hidden. Models are supposed to make system comprehension easier by reducing complexity. Therefore, models should play a key role in education, since they would ease the students' learning process. Although these statements are widely accepted, to the best of our knowledge, there is no empirical evidence that supports these hypotheses (beyond practitioners' personal experience). This article aims to contribute to fill thisgap by performing an empirical study on how well students understand entity-relationship database models as compared to structured query language (SQL) code. Several ER models and their corresponding SQL code (more specifically, the data definition language (DDL) statements required to create such models) were shown to a heterogeneous group of students, who answered different questions about the database systems represented by these artifacts. Then, we analysed the correctness of the answers to check whether the ER models really improved students' comprehension.

SQL Injection Detection and Prevention Techniques

SQL injection is a type of attack which the attacker adds Structured Query Language code to a web form input box to gain access or make changes to data. SQL injection vulnerability allows an attacker to flow commands directly to a web application's underlying database and destroy functionality or confidentiality. Researchers have proposed different tools to detect and prevent this vulnerability. In this paper we present SQL injection attack types and also current techniques which can detect or prevent these attacks. Finally we evaluate these techniques.