What does SELECT followed by a SQL command as a string mean-CodePudding

What does something like

SELECT 'DROP INDEX' || 'some other string'

mean?

I know usually you have the column name after the select, like SELECT first_name, but what does it mean when it is in single quotes? I believe the || means to concatenate the strings. Sorry, I'm new to this and couldn't seem to find anything about this.

EDIT: Sorry I realized my question was not very clear. I meant to ask what does it mean if the SELECT was followed by a SQL command in single quotes? Would it be any different? Would it also just create a column with DROP INDEX as the header? Something like

SELECT 'DROP INDEX' || 'some other string'
FROM ...
WHERE...

CodePudding user response：

Selecting a string will return the string value.

SELECT 'string here'

Will return string here

|| is short hand for concatenation.

SELECT 'string' || ' here'

Will return string here.

EDIT: Selecting a SQL command will return the SQL command in the message. This is often done when creating dynamic SQL and wanting to validate the SQL command before executing it. If you put a SQL command in the @sql NVARCHAR(max) variable, you can SELECT @sql to return the query or EXEC sp_executesql @sql to execute the SQL command (SQL Server syntax).

CodePudding user response：

The idea here is to generate SQL statements from database metadata so that you can execute them as a script.

But if you use the string concatenation operator || to construct the statements, you run the risk of SQL injection.

For example, to delete all tables in a schema, you could generate a script with

SELECT 'DROP TABLE ' || table_schema || '.' || table_name
FROM information_schema.tables
WHERE table_schema = 'my_schema';

Now if there happens to be a table called My-Table, that script would fail, since that is not a standard conforming SQL identifier and would have to be quoted.

And that is also not safe:

SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '"'
FROM information_schema.tables
WHERE table_schema = 'my_schema';

because there could be a table called silly"name.

By crafting tables with names like existing_table"; DROP TABLE "important, you could abuse such a statement to create havoc in the database.

Avoid string concatenation and use format:

SELECT format('DROP TABLE %I.%I', table_schema, table_name)
FROM information_schema.tables
WHERE table_schema = 'my_schema';

CodePudding user response：

SELECT name, 'is a human' AS Note FROM people

The result would be a table with 2 colums: Name and Note. Name would be the person's name and Note would always say 'is a human'.

|| is the logical OR operator.

Links:

Edit: It was not the question but DROP INDEX is a SQL command: https://www.w3schools.com/SQL/sql_ref_drop_index.asp