2

Is there a specified behavior for updating the same column 2+ times in the same UPDATE query, as follows?

UPDATE tbl SET a = 5, b = 'something', a = 6 WHERE c = 'whatever';

Is there a standardized behavior for this, or might it vary between flavors of SQL (e.g. it is "undefined behavior")? A cursory test with sqlite seems to indicate they are executed left-to-right, so the last column value will be the resulting one, but that doesn't imply that will always be the case.

Edit: The reason I'm trying to do this is I'm testing some SQL injection for a class project. One of the fields in an UPDATE is unsafely injected, and I'm trying to use it to overwrite previously SET fields from the same query.

iobender
  • 3,346
  • 1
  • 17
  • 21
  • 1
    Why would you ever want to do this? Perhaps sample data and desired results would help us understand your goal. In `mysql`, it will update with the last value you supply -- `sql server` won't even allow it. What are you trying to accomplish? – sgeddes Mar 03 '16 at 02:54
  • @sgeddes I've updated the post. – iobender Mar 03 '16 at 02:58
  • The order that `set` statements are assigned is not specified in general. A given database might specify the lexical ordering, but this is not guaranteed. – Gordon Linoff Mar 03 '16 at 03:32

1 Answers1

0

This isn't exactly the answer you're looking for but assuming that the text "something" is a field you are passing in and it isn't parameterized or escaped you may be able to do this. This all depends on how the query is being built and what database it is being run against.

UPDATE tbl SET a = 5, b = 'something'; UPDATE tbl set a = 6;--' WHERE c = 'whatever';

by entering the following in the user input

something'; UPDATE tbl set a = 6;--

This assumes that the query is built dynamically something like this

var query = "UPDATE tbl set a = 5, b = '" + userInput + "' WHERE c = 'whatever'";

Here is a relevant question: How does the SQL injection from the "Bobby Tables" XKCD comic work?

Community
  • 1
  • 1
Robert Harris
  • 482
  • 2
  • 6