1

Let's say I have a few hypothetical tables: User, Item, and Sale.

CREATE TABLE User (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE Item (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  upc VARCHAR(255) NOT NULL,
  description VARCHAR(255) NOT NULL,
  price DECIMAL(5,2) NOT NULL
  userId INT NOT NULL,

  FOREIGN KEY(userId) REFERENCES User(id)
);

CREATE TABLE Sale (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  quantity INT NOT NULL,
  total DECIMAL(5,2) NOT NULL,
  itemId INT NOT NULL,

  FOREIGN KEY(itemId) REFERENCES Item(id)
);

Each user can add multiple items, and can sell multiple quantities of each item. A record of each sale is going to go into the Sale table, but I have to make sure that the Item ID being entered into the Sale table is actually owned by the User who is "creating" the entry.

I've thought about a couple of ways of doing this from an application layer (e.g., JDBC).

  1. Do a SELECT to make sure the User owns that Item.

    SELECT id FROM Item WHERE id = ? AND userId = ?
    

    If there is a match (i.e., rows returned), the User owns that Item and can insert a Sale record for that Item. This method seems a bit inefficient, however, since I have to do multiple, separate queries in order to accomplish one task. Having a connection pool (and thus reusing the same connection for each query) will help performance a little, but I'm not sure by how much.

  2. Do a "conditional INSERT" via INSERT ... SELECT:

    INSERT INTO Sale(quantity, total, itemId)
      SELECT 4, 5.00, 3 FROM Dual
        WHERE EXISTS(SELECT id FROM Item WHERE id = ? AND userId = ?);
    

    I really like the idea of this option, but there's an outstanding issue that I haven't been able to work around:

The query itself would be done from an application. Parts of the insert statement are raw values, which are not known until the last second. And since the only way to do a "conditional insert" is to SELECT data from some table (dummy or otherwise), you can't put ? placeholders for a prepared statement for column names.

In other words, the 4, 5.00, and 3 in the above statement are raw values, and the only way I know of to get them into the SQL string is to do concatenation:

String sql = "INSERT INTO Sale(quantity, total, itemId) SELECT "
    + quantity + ", " + total + ...;

Which leaves the door wide open to potential SQL injection attacks. It's a bit trickier to do if the Java variables quantity and total are numeric data types (i.e., can't have quotes), but it's still a loophole that I don't want to leave open.

Is there a good way to accomplish what I'm trying to do efficiently in one SQL statement? Or is the best way option (1) above?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
David Mordigal
  • 813
  • 2
  • 9
  • 22
  • Not sure if MySQL supports this, but have you tried providing parameters in the select-clause, either bare parameters (eg `select ? ...`), or parameters with an explicit cast (eg `select cast(? as integer) ...`)? – Mark Rotteveel Mar 03 '18 at 09:36
  • @MarkRotteveel I have tried that, and it doesn't seem to work. I've read about it too, and MySQL doesn't seem to support prepared statement parameters for column names, see [this question](https://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements) – David Mordigal Mar 03 '18 at 22:33
  • That question you linked is about using parameters to set the column name, not about specifying values though. – Mark Rotteveel Mar 04 '18 at 08:31

0 Answers0