SQL Implementation Research Articles

Deep Learning-Based Detection Technology for SQL Injection Research and Implementation

Amid the incessant evolution of the Internet, an array of cybersecurity threats has surged at an unprecedented rate. A notable antagonist within this plethora of attacks is the SQL injection assault, a prevalent form of Internet attack that poses a significant threat to web applications. These attacks are characterized by their extensive variety, rapid mutation, covert nature, and the substantial damage they can inflict. Existing SQL injection detection methods, such as static and dynamic detection and command randomization, are principally rule-based and suffer from low accuracy, high false positive (FP) rates, and false negative (FN) rates. Contemporary machine learning research on SQL injection attack (SQLIA) detection primarily focuses on feature extraction. The effectiveness of detection is heavily reliant on the precision of feature extraction, leading to a deficiency in tackling more intricate SQLIA. To address these challenges, we propose a novel SQLIA detection approach harnessing the power of an enhanced TextCNN and LSTM. This method begins by vectorizing the samples in the corpus and then leverages an improved TextCNN to extract local features. It then employs a Bidirectional LSTM (Bi-LSTM) network to decipher the sequence information inherent in the samples. Given LSTM’s modest effectiveness for relatively long sequences, we further integrate an attention mechanism, reducing the distance between any two words in the sequence to one, thereby enhancing the model’s effectiveness. Moreover, pre-trained word vector features acquired via BERT for transfer learning are incorporated into the feature section. Comparative experimental results affirm the superiority of our deep learning-based SQLIA detection approach, as it effectively elevates the SQLIA recognition rate while reducing both FP and FN rates.

Initial Optimization Techniques for the Cube Algebra Query Language

A common model used in addressing today's overwhelming amounts of data is the OLAP Cube. The OLAP community has proposed several cube algebras, although a standard has still not been nominated. This study focuses on a recent addition to the cube algebras: the user-centric Cube Algebra Query Language (CAQL). The study aims to explore the optimization potential of this algebra by applying logical rewriting inspired by classic relational algebra and parallelism. The lack of standard algebra is often cited as a problem in such discussions. Thus, the significance of this work is that of strengthening the position of this algebra within the OLAP algebras by addressing implementation details. The modern open-source PostgreSQL relational engine is used to encode the CAQL abstraction. A query workload based on a well-known dataset is adopted, and CAQL and SQL implementations are compared. Finally, the quality of the query created is evaluated through the observed performance characteristics of the query. Results show strong improvements over the baseline case of the unoptimized query.

A Formalization of SQL with Nulls

SQL is the world’s most popular declarative language, forming the basis of the multi-billion-dollar database industry. Although SQL has been standardized, the full standard is based on ambiguous natural language rather than formal specification. Commercial SQL implementations interpret the standard in different ways, so that, given the same input data, the same query can yield different results depending on the SQL system it is run on. Even for a particular system, mechanically checked formalization of all widely-used features of SQL remains an open problem. The lack of a well-understood formal semantics makes it very difficult to validate the soundness of database implementations. Although formal semantics for fragments of SQL were designed in the past, they usually did not support set and bag operations, lateral joins, nested subqueries, and, crucially, null values. Null values complicate SQL’s semantics in profound ways analogous to null pointers or side-effects in other programming languages. Since certain SQL queries are equivalent in the absence of null values, but produce different results when applied to tables containing incomplete data, semantics which ignore null values are able to prove query equivalences that are unsound in realistic databases. A formal semantics of SQL supporting all the aforementioned features was only proposed recently. In this paper, we report about our mechanization of SQL semantics covering set/bag operations, lateral joins, nested subqueries, and nulls, written in the Coq proof assistant, and describe the validation of key metatheoretic properties. Additionally, we are able to use the same framework to formalize the semantics of a flat relational calculus (with null values), and show a certified translation of its normal forms into SQL.

Design and implementation of ETL processes using BPMN and relational algebra

Extraction, transformation, and loading (ETL) processes are used to extract data from internal and external sources of an organization, transform these data, and load them into a data warehouse. The Business Process Modeling and Notation (BPMN) has been proposed for expressing ETL processes at a conceptual level. A different approach is studied in this paper, where relational algebra (RA), extended with update operations, is used for specifying ETL processes. In this approach, data tasks in an ETL workflow can be automatically translated into SQL queries to be executed over a DBMS. To illustrate this study, the paper addresses the problem of updating Slowly Changing Dimensions (SCDs) with dependencies, that is, the case when updating a SCD table impacts on associated SCD tables. Tackling this problem requires extending the classic RA with update operations. The paper also shows the implementation of a portion of the TPC-DI benchmark that results from both approaches. Thus, the paper presents three implementations: (a) An SQL implementation based on the extended RA-based specification of an ETL process expressed in BPMN4ETL; and (b) Two implementations of workflows that follow from BPMN4ETL, one that uses the Pentaho DI tool, and another one that uses Talend Open Studio for DI. Experiments over these implementations of the TPC-DI benchmark for different scale factors were carried out, and are described and discussed in the paper, showing that the extended RA approach results in more efficient processes than the ones produced by implementing the BPMN4ETL specification over the mentioned ETL tools. The reasons for this result are also discussed.

SQL and Islamic Geometric Design

SQL is used as the principle software engineering tool to aid the production of a database of Islamic patterns. These patterns can be found by searching facilities using geometric properties, such the symmetry. The SQL is used to automatically generate HTML for the web site (www.tilingsearch.org). The database also covers aspects of the internal documentation which includes the mathematical analysis of the patterns, and photos, if available. Checks are also performed using SQL to verify aspects of the system. The open source implementation of SQL is used to allow the entire system to be run on any computer. Currently, there is no SQL server on the web site, but a simple set of options is used to aid searching.

NIS-Apriori-based rule generation with three-way decisions and its application system in SQL

In the study, non-deterministic information systems-Apriori-based (NIS-Apriori-based) rule generation from table data sets with incomplete information, SQL implementation, and the unique characteristics of the new framework are presented. Additionally, a few unsolved new research topics are proposed based on the framework. We follow the framework of NISs and propose certain rules and possible rules based on possible world semantics. Although each rule τ depends on a large number of possible tables, we prove that each rule τ is determined by examining only two τ-dependent possible tables. The NIS-Apriori algorithm is an adjusted Apriori algorithm that can handle such tables. Furthermore, it is logically sound and complete with regard to the rules. Subsequently, the implementation of the NIS-Apriori algorithm in SQL is described and a few new topics induced by effects of NIS-Apriori-based rule generation are confirmed. One of the topics that are considered is the possibility of estimating missing values via the obtained certain rules. The proposed methodology and the environment yielded by NIS-Apriori-based rule generation in SQL are useful for table data analysis with three-way decisions.

Business-oriented customized big data query system and its SQL parser design and implementation

This paper proposes a big data query system for customized queries based on specific business needs. This paper introduces the components and structure of the query system. ANTLR tools are used as language recognizer to design and implement a customized SQL dialect. The system builds a simpler and easier query interface on Spark SQL, which satisfies the query requirements of the Internet user behavior analysis platform.

A comparison of different database technologies for the CMS AsyncStageOut transfer database

AsyncStageOut (ASO) is the component of the CMS distributed data analysis system (CRAB) that manages users transfers in a centrally controlled way using the File Transfer System (FTS3) at CERN. It addresses a major weakness of the previous, decentralized model, namely that the transfer of the user’s output data to a single remote site was part of the job execution, resulting in inefficient use of job slots and an unacceptable failure rate.Currently ASO manages up to 600k files of various sizes per day from more than 500 users per month, spread over more than 100 sites. ASO uses a NoSQL database (CouchDB) as internal bookkeeping and as way to communicate with other CRAB components. Since ASO/CRAB were put in production in 2014, the number of transfers constantly increased up to a point where the pressure to the central CouchDB instance became critical, creating new challenges for the system scalability, performance, and monitoring. This forced a re-engineering of the ASO application to increase its scalability and lowering its operational effort.In this contribution we present a comparison of the performance of the current NoSQL implementation and a new SQL implementation, and how their different strengths and features influenced the design choices and operational experience. We also discuss other architectural changes introduced in the system to handle the increasing load and latency in delivering output to the user.

Query optimization through the looking glass, and what we found running the Join Order Benchmark

Finding a good join order is crucial for query performance. In this paper, we introduce the Join Order Benchmark that works on real-life data riddled with correlations and introduces 113 complex join queries. We experimentally revisit the main components in the classic query optimizer architecture using a complex, real-world data set and realistic multi-join queries. For this purpose, we describe cardinality-estimate injection and extraction techniques that allow us to compare the cardinality estimators of multiple industrial SQL implementations on equal footing, and to characterize the value of having perfect cardinality estimates. Our investigation shows that all industrial-strength cardinality estimators routinely produce large errors: though cardinality estimation using table samples solves the problem for single-table queries, there are still no techniques in industrial systems that can deal accurately with join-crossing correlated query predicates. We further show that while estimates are essential for finding a good join order, query performance is unsatisfactory if the query engine relies too heavily on these estimates. Using another set of experiments that measure the impact of the cost model, we find that it has much less influence on query performance than the cardinality estimates. We investigate plan enumeration techniques comparing exhaustive dynamic programming with heuristic algorithms and find that exhaustive enumeration improves performance despite the suboptimal cardinality estimates. Finally, we extend our investigation from main-memory only, to also include disk-based query processing. Here, we find that though accurate cardinality estimation should be the first priority, other aspects such as modeling random versus sequential I/O are also important to predict query runtime.

Data security approach in cloud computing by using DOS attack

This paper analyzed the security measures in the collaborative environment, identified widespread DDoS attack implements and surveyed the current trace back tools to suggestion the actual attacker. Nearly, the most popular fears in cooperative environment are entrance controller attacks, SQL injection attacks and implementation of malevolent software, object reusability, camouflaged attacks, inhaling, nosey, and denial-of-service attacks (DoS). The elementary obligation of network based resource allocation includes providing dependable and reliable access of local as well as remote resources in collaborative application. But DDoS attacks are coming in the way of success of such resource centers. DDoS attack architecture can be classified as agent handler model and internet relay chat (IRC) model.

Linearized and single-pass belief propagation

How can we tell when accounts are fake or real in a social network? And how can we tell which accounts belong to liberal, conservative or centrist users? Often, we can answer such questions and label nodes in a network based on the labels of their neighbors and appropriate assumptions of homophily ("birds of a feather flock together") or heterophily ("opposites attract"). One of the most widely used methods for this kind of inference is Belief Propagation (BP) which iteratively propagates the information from a few nodes with explicit labels throughout a network until convergence. A well-known problem with BP, however, is that there are no known exact guarantees of convergence in graphs with loops. This paper introduces Linearized Belief Propagation (LinBP), a linearization of BP that allows a closed-form solution via intuitive matrix equations and, thus, comes with exact convergence guarantees. It handles homophily, heterophily, and more general cases that arise in multi-class settings. Plus, it allows a compact implementation in SQL. The paper also introduces Single-pass Belief Propagation (SBP), a localized (or "myopic") version of LinBP that propagates information across every edge at most once and for which the final class assignments depend only on the nearest labeled neighbors. In addition, SBP allows fast incremental updates in dynamic networks. Our runtime experiments show that LinBP and SBP are orders of magnitude faster than standard BP, while leading to almost identical node labels.

판단 트리 분류를 위한 SQL 기초 기능의 구현에 관한 연구

Decision tree classification is one of the important problems in data mining fields and data minings have been important tasks in the fields of large database technologies. Therefore the coupling efforts of data mining systems and database systems have led the developments of database primitives supporting data mining functions such as decision tree classification. These primitives consist of the special database operations which support the SQL implementation of decision tree classification algorithms. These primitives have become the consisting modules of database systems for the implementations of the specific algorithms. There are two aspects in the developments of database primitives which support the data mining functions. The first is the identification of database common primitives which support data mining functions by analysis. The other is the provision of the extended mechanism for the implementations of these primitives as an interface of database systems. In data mining, some primitives want be stored in DBMS is one of the difficult problems. In this paper, to solve of the problem, we describe the database primitives which construct and apply the optimized decision tree classifiers. Then we identify the useful operations for various classification algorithms and discuss the implementations of these primitives on the commercial DBMS. We implement these primitives on the commercial DBMS and present experimental results demonstrating the performance comparisons.

Подход к интеграции интеллектуального анализа данных в реляционную СУБД на основе генерации текстов хранимых процедур

The paper introduces an approach to integration of data mining algorithms with relational DBMS. Approach assumes using XML-based markup for data mining algorithms implemented in SQL. User specifies input tables and algorithm parameters. After that system generates persistent stored procedures for specific data mining algorithm. Proposed markup language is described with examples. If there are several algorithms for some data mining problem the most effective algorithm implementation in SQL is generated by means of DBMS command EXPLAIN which shows estimated execution cost. Module structure and API for proposed program system is described.

Tenzing a SQL implementation on the MapReduce framework

Tenzing is a query engine built on top of MapReduce [9] for ad hoc analysis of Google data. Tenzing supports a mostly complete SQL implementation (with several extensions) combined with several key characteristics such as heterogeneity, high performance, scalability, reliability, metadata awareness, low latency, support for columnar storage and structured data, and easy extensibility. Tenzing is currently used internally at Google by 1000+ employees and serves 10000+ queries per day over 1.5 petabytes of compressed data. In this paper, we describe the architecture and implementation of Tenzing, and present benchmarks of typical analytical queries.

Formula omitted] multi-dimensional aggregation

The SQL:2003 standard introduced window functions to enhance the analytical processing capabilities of SQL. The key concept of window functions is to sort the input relation and to compute the aggregate results during a scan of the sorted relation. For multi-dimensional OLAP queries with aggregation groups defined by a general θ condition an appropriate ordering does not exist, though, and hence expensive join-based solutions are required. In this paper we introduce θ ‐constrained multi-dimensional aggregation ( θ ‐MDA ), which supports multi-dimensional OLAP queries with aggregation groups defined by inequalities. θ ‐MDA is not based on an ordering of the data relation. Instead, the tuples that shall be considered for computing an aggregate value can be determined by a general θ condition. This facilitates the formulation of complex queries, such as multi-dimensional cumulative aggregates, which are difficult to express in SQL because no appropriate ordering exists. We present algebraic transformation rules that demonstrate how the θ ‐MDA interacts with other operators of a multi-set algebra. Various techniques for achieving an efficient evaluation of the θ ‐MDA are investigated, and we integrate them into concrete evaluation algorithms and provide cost formulas. An empirical evaluation with data from the TPC-H benchmark confirms the scalability of the θ ‐MDA operator and shows performance improvements of up to one order of magnitude over equivalent SQL implementations.

Optimization of Linear Recursive Queries in SQL

Recursion is a fundamental computation mechanism which has been incorporated into the SQL language. This work focuses on the optimization of linear recursive queries in SQL. Query optimization is studied with two important graph problems: computing the transitive closure of a graph and getting the power matrix of its adjacency matrix. We present SQL implementations for two fundamental algorithms: seminaive and direct. Five query optimizations are studied: 1) storage and indexing; 2) early selection; 3) early evaluation of nonrecursive joins; 4) pushing duplicate elimination; and 5) pushing aggregation. Experiments compare both evaluation algorithms and systematically evaluate the impact of optimizations with large input tables. Optimizations are evaluated on four types of graphs: binary trees, lists, cyclic graphs, and complete graphs, going from the best to worst case. In general, Seminaive is faster than direct, except for complete graphs. Storing and indexing rows by vertex and pushing aggregation work well on trees, lists, and cyclic graphs. Pushing duplicate elimination is essential for complete graphs, but slows computation for acyclic graphs. Early selection with equality predicates significantly accelerates computation for all types of graphs.

Automated analysis of neuronal morphologies via python and (collaborative) open source databases

Event Abstract Back to Event Automated analysis of neuronal morphologies via python and (collaborative) open source databases Nina Hubig1, Hans J. Ohlbach1, Thomas Wachtler2 and Philipp L. Rautenberg2* 1 Ludwig-Maximilians-Universität München, Institute for Computer Science, Germany 2 Ludwig-Maximilians-Universität München, Department of Biology, Germany Neuronal morphology plays a fundamental role for the information processing capabilities of neurons. To fully exploit the information gained by neuron reconstructions, it is crucial for neuroscientific researchers to analyze a large amount of data in a fast, effective, and automated way. We developed a new system for the analysis of morphological data that is based on a high-level object-oriented programming language that was integrated into an object-relational database management system. This approach enables to connect automatized data analysis procedures directly to the stored data. In our (experimental) system we used Python as object oriented language which is commonly used in the neuroscientific community. It is a versatile language that can be easily applied to solve several tasks that research laboratories face almost every day: data manipulation, biological data retrieval and parsing, automation, or simulations of single cells or neural networks [1]. As a database system we used PostgreSQL [2]. PostgreSQL is a powerful, open source object-relational database system. Its SQL implementation strongly conforms to the ANSI-SQL:2008 standard and therefore is widely compatible with other database systems. For efficient data access, we defined a data schema representing SWC-specifications [3]. Based on this schema, we implemented views and analysis functions in SQL, to enable data analysis directly within the database. Analyses were performed using database and computing services at the German INCF Node (www.g-node.org). Different properties of the morphology of neurons, such as total length, number of branch points, surface area, and volume were calculated and compared across neurons in the database, taking into account other properties, like age or species of the corresponding animal. For example, we found that the ratio between surface area and volume is an important measurement of the influence of a neurons morphology on electrophysiological properties. The results can be accessed from the database for visualization by various well-known software-systems like MATLAB or OpenOffice. Our results are presented in a portable and easy way via database access and web interface. This yields the requisite for efficient data analysis: Morphologies can be easily exchanged and thus a great variety of data analysis can be performed in a systematic and non-redundant fashion. This eliminates the need of doing the same analysis by hand again for each morphology. It also in turn facilitates analysis and visualization of results. The method is not restricted to analysis of neuron morphology, but could also be applied for the analysis of physiological data. Acknowledgments: The German INCF Node is supported byBMBF grant 01GQ0801

A NEW FEASIBLE NATURAL LANGUAGE DATABASE QUERY METHOD

A metadata search approach is proposed to provide practical solutions to the natural language database query problem; where the metadata grow in largely linear manner and the search is linguistics-free. A new class of reference dictionary integrates four types of enterprise metadata: enterprise information models, database values, user-words, and query cases. The layered and scalable information models allow user-words to stay in original forms as users articulated them, as opposed to relying on permutations of individual words contained in the original query. A graphical representation method turns the dictionary into searchable graphs representing all possible interpretations of the input. A branch-and-bound algorithm then identifies optimal interpretations, which lead to SQL implementation of the original queries. Query cases enhance both the metadata and the search of metadata, as well as providing case-based reasoning to directly answer the queries. This design assures feasible solutions at the termination of the search, even when the search is incomplete (i.e., the results contain the correct answer to the original query). The necessary condition is that the text input contains at least one entry in the reference dictionary. The sufficient condition is that the text input contains a set of entries corresponding to a complete, correct single SQL query. Laboratory testing shows that the system obtained accurate results for most cases that satisfied only the necessary condition.

Integrating K-means clustering with a relational DBMS using SQL

Integrating data mining algorithms with a relational DBMS is an important problem for database programmers. We introduce three SQL implementations of the popular K-means clustering algorithm to integrate it with a relational DBMS: 1) a straightforward translation of K-means computations into SQL, 2) an optimized version based on improved data organization, efficient indexing, sufficient statistics, and rewritten queries, and 3) an incremental version that uses the optimized version as a building block with fast convergence and automated reseeding. We experimentally show the proposed K-means implementations work correctly and can cluster large data sets. We identify which K-means computations are more critical for performance. The optimized and incremental K-means implementations exhibit linear scalability. We compare K-means implementations in SQL and C++ with respect to speed and scalability and we also study the time to export data sets outside of the DBMS. Experiments show that SQL overhead is significant for small data sets, but relatively low for large data sets, whereas export times become a bottleneck for C++.

물류 차량 관제를 위한 SQL 기반 이동 객체 질의 처리 시스템의 설계 및 구현

물류 차량 관제를 위한 SQL 기반 이동 객체 질의 처리 시스템의 설계 및 구현