0

I have read the other questions and all of the posted comments/answers to questions that are similar to this one. None of them seem to answer this question directly.

I am wanting to know if there is a way to either concatenate or place an INSERT, DELETE or UPDATE statement into a SELECT statement.

If so how? Could this be done by using a derived table function or a subSelect statement?

And example would be:

INSERT INTO Customer (name)
VALUES 'Test Name'

but place this into a SELECT statement

Example pseudo code:

SELECT *
FROM Customer
WHERE
Customer.name = (INSERT INTO Customer (name) VALUES 'Test Name')

The big thing here is getting the INSERT statement into the SELECT statement in an unconventional way.

Thank you and hopefully this will strike up some good conversation/ideas/results!

Reason for wanting to know this:

  1. Our current DBMS (Fishbowl) does not allow us to use an UPDATE, DELETE or INSERT statement with is SQL compiler and we are wanting to mass alter our fields/clean up our tables.
  2. To know if it is possible
Ashton
  • 363
  • 1
  • 4
  • 21
  • 1
    Do you have a specific problem you're trying to solve or is this an exercise? – n8wrl May 06 '16 at 18:54
  • 2
    This syntax is not allowed in any database, so I guess the answer is "no". Postgres does do something quite similar, where DML statements are allowed in CTEs. MySQL does not have any similar capability within a single statement. – Gordon Linoff May 06 '16 at 18:54
  • @n8wrl This is the big picture "problem" to something I am currently working on that is less related. This is not an assignment or anything. In fact, I have never seen this done before and after pondering and experiementing I was curious if this idea/concept could be done. – Ashton May 06 '16 at 18:55
  • 1
    are you trying to generate a script maybe something like this? `select 'INSERT INTO' || customer.name || ' VALUES 'Test Name' ' from customer ` – msheikh25 May 06 '16 at 18:56
  • @GordonLinoff I understand and that is why i figured the question would actually be worth asking/discussing because just like how SQL Injections use user input statements to alter/loop requests in SQL- if a DELETE, INSERT or UPDATE statement could be masked withtin a SELECT statement this could also be something to note. Just me thinking of course! (adding this to the main post). – Ashton May 06 '16 at 18:57
  • @mo2 Hmmm... interesting idea. I am not attempting to generate a script but could be this entered into a normal database SQL compiler? I actually am using a DBMS that does not allow for us to alter our data directly through their DBMS's compiler because it will not accept the INSERT or DELETE functions. It allows us to only use a SELECT. This would be helpful because we are attempting to mass alter our fields. – Ashton May 06 '16 at 19:01
  • the SQL injection takes place by preceding the INSERT statement with something like '; in order to finish the SELECT statement and then finishing it with a -- in order to transform whatever was there before into a comment – Tudor Constantin May 06 '16 at 19:02
  • It's not clear what you're asking. What is the underlying goal? Are you trying to do an insert and immediately get those results back as a result set? I don't see how SQL injection is relevant here. If your query was vulnerable then an INSERT statement could probably be snuck in. – Mark Leiber May 06 '16 at 19:02
  • 2
    I guess his goal is to do an insert into a DB where his user has only SELECT rights – Tudor Constantin May 06 '16 at 19:03
  • @MarkLeiber You are correct Injection is not complelty relevant but I used it as an example for the potential way of performing what I am wanting to do which is exactly what Tudor said. I am trying to figure out a way to use an INSERT where my DBMS only allows for sql queries to start with SELECT statements – Ashton May 06 '16 at 19:05
  • @TudorConstantin Exactly. You're correct and that is why I used injection as an example way to think out loud as to how I would do this. – Ashton May 06 '16 at 19:06
  • 1
    @Ashton If you don't have access to insert or delete, then you wouldn't be able to run the script after its generated. You could come up with a proc that would generate and execute the script and hand it off or schedule it via a user that does have the privileges. This has been the approach I've used in the past when I needed to do mass updates/inserts based on data and/or criteria from DB tables. – msheikh25 May 06 '16 at 19:10
  • 1
    Connect to the Firebird database that sits under Fishbowl using an account that has the proper permissions to run the DML statements. – Mark Leiber May 06 '16 at 19:15
  • @mo2 Dang. I was hoping this would work. If you could better explain your second example and maybe place it as an answer that way I could give you credit for sharing? Thanks to everyone who has commented! – Ashton May 06 '16 at 19:16
  • @MarkLeiber That idea seems like one that could work. Would you mind posting it as an answer and explaining it a tad more for me? That way i can give you credit for it. Thanks man – Ashton May 06 '16 at 19:18

1 Answers1

1

You may have a select within an insert but not the other way around.

A select within an insert statement could be used to copy values from a table to another. (You are just writing into a table after a read from another)

But an insert within a select doesn't make sense! What are you going to insert when you are running a select from a table(or just reading data)?(how is it possible to write when you are only allowed to read?)

Your situation, if you don't have access to run an insert then it doesn't matter where you put the statement, you just can't because you aren't allowed to!

But if you are talking about your query engine/database wrapper not allowing a direct insert, then its probably because it requires an application/program to insert data into to rather than just a query(as your engine doesn't already have the capability to perform that operation).

Ani Menon
  • 27,209
  • 16
  • 105
  • 126
  • You are right it does not make sense! That is why I wanted to entertain the idea to see what else would come out of it. My curerent DBMS does not allow me to use anything but SELECT statements in it's compiler and I wanted a way around this. Thank you for your input/answer! – Ashton May 06 '16 at 19:22