Beyond Text-to- SQL for I o T Defense: A Comprehensive Framework for Querying and Classifying I o T Threats (2025)

Abstract: Recognizing the promise of natural language interfaces to databases, prior studies have emphasized the development of text-to-SQL systems. Existing research has generally focused on generating SQL statements from text queries, and the broader challenge lies in inferring new information about the returned data. Our research makes two major contributions to address this gap. First, we introduce a novel Internet-of-Things (IoT) text-to-SQL dataset comprising 10,985 text-SQL pairs and 239,398 rows of network traffic activity. The dataset contains additional query types limited in prior text-to-SQL datasets, notably, temporal-related queries. Our dataset is sourced from a smart building’s IoT ecosystem exploring sensor read and network traffic data. Second, our dataset allows two-stage processing, where the returned data (network traffic) from a generated SQL can be categorized as malicious or not. Our results show that joint training to query and infer information about the data improves overall text-to-SQL performance, nearly matching that of substantially larger models. We also show that current large language models (e.g., GPT3.5) struggle to infer new information about returned data (i.e., they are bad at tabular data understanding), thus our dataset provides a novel test bed for integrating complex domain-specific reasoning into LLMs.

## introduction relational databases contain vast quantities of structured knowledge, often having trillions of rows of data, spanning diverse domains from healthcare and finance to entertainment and education. while structured query languages (sql) provide database experts the resources to extract, manipulate, and reason over this data, many potential users remain cut off from direct access due to the steep learning curve of mastering these languages. the importance of making data more accessible a system that seamlessly translates natural language queries into sql (text-to-sql) not only democratizes access to data but also has the potential to drastically reduce the time to insights for diverse stakeholders, including managers, analysts, educators, and the general public. there have been many advances in translating natural language to sql @xcite @xcite @xcite @xcite @xcite . recent work has focused on either fine-tuning transformers or on the use of pre-built large language 1 models (e.g., chatgpt) with prompt tuning and in-context examples. for example, @xcite explored in-context learning using chatgpt to generate sql statements, and @xcite explored zero-shot text-to-sql generation using chatgpt. @xcite developed a unified framework using fine-tuning for text-to-sql generation, leveraging relation-aware self-attention, to tackle schema encoding, schema linking, and feature representation. combined with bert data augmentation, this framework yielded a remarkable exact match accuracy of 65.6% on the spider dataset. much of the prior work on text-to-sql generation has focused on simply generating sql statements from the input text queries. some recent work has expanded on standard studies by exploring conversational text-to-sql tasks @xcite . intuitively, @xcite developed a system that can ask follow-up questions to answer ambiguous queries better, verify returned results, and notify users of unanswerable queries. however, there is limited work that can query a database and make inferences (understand) the returned data. follow-up questions may involve making inferences and returning results that are not directly within the database. hence, translating natural language to sql is only half the challenge. the true power of such a system lies in its ability to retrieve and infer new information about the data returned. this ensures that the insights drawn from databases are accurate and meaningful. for instance, in an educational context, a student might not only ask for the number of historical events in a given time but might also want to know their significance or interconnections, requiring a depth of reasoning beyond retrieval. at a high level, our work combines two lines of research not explored in previous papers: tabular data classification and question answering using transformers @xcite ) and text-to-sql generation. there has been some recent work about predicting various aspects of tabular data. for example, @xcite predicts whether a claim is true or false given an input table. likewise, @xcite developed a system to inform missing or corrupted data within a table. however, much of this work assumes the table is provided. hence, we develop a new text-to-sql dataset to make predictions/inferences about the data and query the data using a single model. an example of our task is provided in figure 1 . as a case study, our dataset consists of internet-of-things (iot) data from a smart building setting. specifically, we assume a centralized database that captures both network traffic about the iot devices and sensor readings (temperature, humidity, co2 levels, etc.). the sql statements query the iot databases to return relevant data. the reasoning component of our dataset is specific to the network data. we classify the network traffic as malicious (e.g., ddos attacks, botnet activity, etc.) or benign (non-malicious activity). our decision to use iot data is due to the following reasons. first, iot data has a huge temporal component @xcite . there have been limited text-to-dataset resources that contain many temporal-related queries (e.g., spider is based on sqlite databases and does not support datetime columns). second, making inferences about network traffic data is non-trivial and has not been explored in the nlp community. in summary, the contributions of this paper are as follows: (i) we introduce a new iot-sql dataset containing 10,985 unique text-sql pairs and 239,398 rows of network traffic activity from zeek logs with annotations for malicious and nonmalicious activity (e.g., ddos attacks). this dataset provides a new test bed for text-to-sql models and llms towards both querying data an actually understanding it. specifically, current state-of-the-art llms gpt3.5 fail to perform well on this dataset for the reasoning component. foot_0 (ii) we evaluate the performance of text-to-sql models that can jointly query and reason about the data (i.e., predict whether specific network traffic is malicious). our results suggest that modeling both tasks together substantially improves text-to-sql performance with limited impact on network-traffic malicious activity detection. (iii) we perform error analysis and provide examples of how jointly training to query and understand the data improved sql generation. recent efforts have also been made to develop new datasets beyond traditional text-to-sql pairs. @xcite , for example, collected a conversation-like corpus where a system can ask follow-up questions to answer ambiguous queries better, verify returned results, and notify users of unanswerable queries. similarly, researchers have also focused on curating data (text-sql pairs) that capture items missing in previous datasets (e.g., temporal-related queries). for example, @xcite introduced a new dataset called tempq4nlidb that contains 389 temporal-related question-sql pairs to overcome limitations in existing datasets (e.g., spider). our research expands on this work, containing more than 1,000 temporally-related queries using mysql datetime columns. text-to-sql methods. the field of text-to-sql is concerned with automatically translating natural language queries into structured sql queries. recent advancements in neural network models have led to significant improvements in the accuracy and efficiency of text-to-sql systems @xcite @xcite @xcite @xcite @xcite . recent work has focused on fine-tuning transformers or using pre-built large language models (e.g., chatgpt) with prompt tuning and incontext examples. for example, @xcite explored in-context learning using chatgpt to generate sql statements, and @xcite explored zero-shot text-to-sql generation using chatgpt. @xcite also proposed a relation-aware self-attention mechanism for text-to-sql generation, achieving an accuracy of 65.6% on the spider dataset when combined with bert @xcite . in another independent work, @xcite introduced the picard method, which uses incremental parsing for fine-tuning formal languages. this led to stateof-the-art results on both the spider and cosql datasets. @xcite introduced a novel approach to schema linking using the poincaré distance metric. their results established a new benchmark in performance, outperforming rule-based methods across multiple datasets and showcasing the effectiveness of their probing method. a more recent thorough analysis of the codex language model's text-to-sql abilities was undertaken by @xcite , whose findings highlighted the model's competitive performance across benchmarks, even without finetuning. particularly on the spider benchmark, codex achieved an accuracy of up to 67%. their work also indicated that using a small set of in-domain examples could boost codex's performance beyond some finetuned stateof-the-art models. tabular data understanding. there has been a wide array of papers about understanding tabular data beyond text-to-sql @xcite . according to @xcite , there are six common tabular data tasks: fact-checking, question answering, semantic parsing (i.e., text-to-sql), table retrieval, table metadata prediction, and table content population. fact-checking related work has generally focused on predicting whether a statement/claim is factual, given the knowledge available in a table @xcite . table re trieval research has focused on finding a table that contains the answer to a particular question @xcite . table metadata prediction involves predicting information about the table, such as the column name or a relation between two columns @xcite . finally, table content population involves filling the cells within a table because of missing or incorrect data @xcite . intuitively, our task can be considered a combination of semantic parsing and table content population. the former (semantic parsing) is the text-to-sql task, and the table population we are predicting is malicious or benign information for network traffic. we can think of the malicious information as a missing column in the database. but, more importantly, this is a highly specialized task that large language models cannot easily reason about. hence, our dataset provides a unique research test bed for integrating highly specialized knowledge into llms for tabular qa. ## data in this section, we describe the data creation process for text-sql pairs, the source of the network traffic and sensor data, and how the network traffic data was organized for training our malicious network traffic activity detection model. as shown in figure 2 , the data curation pipeline comprises five major steps. first, we curate the data for the database. second, we "annotate" text-sql pairs. third, we partition network traffic data from the database to be used to train and evaluate a malicious traffic detector. fourth, we review the text-sql pairs, removing incorrect, irrelevant, or unclear queries. moreover, we paraphrase each text-sql pair to provide diversity in how things are specified. finally, we perform an additional round of review after the paraphrase process. ## method in this section, we describe the approach we developed to address the text-to-sql task and malicious traffic detection tasks jointly. schema for text-to-sql. the table schema must be included with the model input to train a model to generate sql queries specific to our database. the schema includes all tables and variables from our database (iot and sensor data). formally, let t i represent a table i, and let c i,j represent a column j in table i. each column has an attribute a i,j representing the j-th column's datatype in table i. for instance, we have the table conn.log, which stores information about connections/sessions. two columns within conn.log include orig_h and orig_p. the attribute assigned to the orig_h column is text since it contains strings (ip addresses). the attribute assigned to orig_p is number (representing the port number). given all of the tables, columns, and attributes in a database, we generate the schema represented in the form of @xmath0, t 1 , c 1,1 , a 1,1 , c 1,2 , a 1,2 , t 2 , c 2,1 , a 2,1 , . . .]. in practice, this looks like @xmath1, conn.log, orig_p, text, . . ., weird.log, orig_p, text, . . .]. we concatenate the schema to each input text before being passed to the t5 models to generate the sql statements. input for malicious traffic detection. instead of passing the schema and text as input for malicious traffic detection, as we do for the text-to-sql generation, we pass an instruction and formatted tabular data. let p represent the instruction and t represent the formatted tabular data. we concatenate both to form the input @xmath2. this work uses the instruction "is the following network information malicious?". also, the tabular data (row) is formatted as t 1 t 2 • • • t n , where each tabular data column/value is represented as a string. moreover, everything is concatenated using space as the delimiter. in practice, this looks like "192.168.1.1 80 192.161.2.2 8080 • • •." note that there are no spaces in the values available in the conn.log file, which contains the network data used for malicious traffic detection. if this work is expanded to other zeek logs, other delimiters would need to be explored. training. to train the model, we fine-tune the flan-t5-base @xcite model. the model is trained using the adam optimizer (kingma and ba, 2014) with a minibatch size 4 and a learning rate .0001. we trained the model for a total of 15 epochs. the model was trained by simply combining the data formatted as described in sections 4. ## results in this section, we describe the evaluation metrics, our baseline models, and the results for text-to-sql prediction and malicious network traffic detection. we also provide an informative error analysis. evaluation metrics. we use two primary metrics for evaluating the text-to-sql results: logical accuracy and execution accuracy. logical accuracy assesses the correctness of the logical structure and semantics of the generated sql with the target sql (i.e., measuring whether two sql queries are exactly the same). however, a potential pitfall of relying solely on logical accuracy is that two queries may be correct but written differently. on the other hand, execution accuracy evaluates the results obtained when the generated sql is run on a database. this metric is vital because the ultimate goal is to extract accurate information from the database, regardless of the sql's structure. however, a high execution accuracy doesn't guarantee that the sql query is optimal or semantically correct. it's possible for an inefficient or technically incorrect query to yield the desired results that are returned by the ground-truth query. hence, we consider both logical and execution accuracy in our study. we use the standard classification metrics macro-precision, macro-recall, and macro-f1 to evaluate our models' malicious network traffic detection performance. baseline models. we explore two major baselines to evaluate the performance of detecting malicious web traffic: support vector machines (svm) and random forest. the input of the models includes all of the features listed in text-to-sql. in malicious network traffic detection. in error analysis. why does the t5-base model match and outperform the t5-large model when trained on both datasets? our analysis shows that much of the improvement is on the conn.log-related queries. the conn.log was the table used as input when training the malicious network traffic detection-related aspect of our model. specifically, for logical accuracy, 142 examples in the test dataset contained items related to the conn.log table. the t5-base model missed 42 of them. the jointly trained t5-base model only missed 27. some of the errors were major, where the t5-base model did not generate a sql statement at all, where the t5-large model returned the correct statement (e.g., "select service from iot23_conn_log group by service hav-ing avg(resp_bytes) >= 829"). we also hypothesize that while we did not train to make inferences about other tables in the database, by better understanding the conn.log table, the model can better understand how it relates more to other tables via join queries. this better understanding of table relationships potentially results in improvements for other tables as well. ## conclusion databases hold large amounts of structured knowledge across various sectors, and efficient access to this data is essential. our study was driven by the goal of nlidb, which is to simplify data access beyond the complexities of sql. while there have been advancements in text-to-sql systems, our research emphasizes the importance of retrieving and understanding the data. with the introduction of the iot-sql dataset, we've provided a unique resource with the ability to predict aspects not in the database (i.e., malicious network activity) and generate sql statements based on an input text query. moreover, the dataset contains many temporal queries that are missing or limited in prior text-to-sql datasets. our findings show that models trained to query and reason about data improve sql generation performance. overall, there are two major avenues for future work. first, we plan to explore more complex models on the dataset, particularly on more complex training, validation, and test sets. for example, recent work suggests that exploring different data split methods (e.g., based on sql length, tables, or column names) can improve the measure of generalizability @xcite . second, we will explore more sophisticated methods of detecting malicious network activity. malicious activity may be related to multiple sessions within the zeek conn.log. developing a system that can reason over multiple rows in the database can potentially generate substantial improvements.