Any way to detect the type of sql query being fired by the user?

Hey Everybody,

I am workin on a project where I am taking sql query as input from the user and returning the results of it. Is there any way to check if the query being submitted is a "select" or an "insert/delete/update" etc?

Any help is very appreciated.

LaryAI

3 years ago

Level 58

You can use the preg_match() function to detect the type of SQL query being fired by the user. For example, you can use the following code to detect if the query is a SELECT query:

if (preg_match('/^\s*SELECT\s/i', $query)) {
    // Query is a SELECT query
}

You can also use the strtoupper() function to convert the query to uppercase and then use strpos() to check if the query contains any of the other SQL keywords (e.g. INSERT, UPDATE, DELETE). For example:

$query = strtoupper($query);
if (strpos($query, 'INSERT') !== false) {
    // Query is an INSERT query
}

tisuchi

3 years ago

Level 70

@madprabh How about this?

$sql = "SELECT * FROM users";

$query_type = strtoupper(substr(trim($sql), 0, 6));

if ($query_type == "SELECT") {
    // It's a SELECT query
} else {
    // It's a non-SELECT query
}

madprabh

3 years ago

Level 8

@tisuchi Thanks for the suggestion but what happens when you have something like this (like you use CTEs etc)

with temp_table as (
 select * from actual_table
)
select * from temp_table

I want to be able to detect and differentiate from select (that leads to a tabular data) vs other queries such as insert/delete/update which doesn't lead to a tabular data.

1 like

Snapey

3 years ago

Level 122

You must REALLY trust your users since they could easily manipulate any table or drop tables ?

madprabh

3 years ago

Level 8

@Snapey The thing is the users will only be able to do it on database that they add in their respective accounts. Something like SQL client. Do you have any suggestions on what I can do to be as safe as possible?

psrz

3 years ago

Level 10

ouch.... talk about sql injection, lol

Make views with the tables you want them to query, maybe limit the data they can see (fields, records)
Set up a database user with only select privileges on those views and NOTHING else
Make a second laravel connection with that user to send those user input queries. NEVER use the main connection with that.

In that scenario if users feel like trying an insert or update it won't matter. I mean, I am assuming you want them to use only selects statement, right ?

Please or to participate in this conversation.