MySQL error: `query': Duplicate entry '' for key 3 (Mysql2::Error) on a Ruby file

Question

This is the code I am using

# update db
client = Mysql2::Client.new(:host => "localhost", :username => "jo151", :password => "password", :database => "jo151")

details.each do |d|
    if d[:sku] != ""
        price = d[:price].split

        if price[1] == "D"
            currency = 144
        else
            currency = 168
        end

        cost = price[0].gsub(",", "").to_f

        if d[:qty] == ""
            qty = d[:qty2]
        else
            qty = d[:qty]
        end 

        results = client.query("SELECT * FROM jos_virtuemart_products WHERE product_sku = '#{d[:sku]}' LIMIT 1;")
        if results.count == 1
            product = results.first


                            client.query("UPDATE jos_virtuemart_products SET product_sku = '#{d[:sku]}', product_name = '#{d[:desc]}', product_desc = '#{d[:desc]}', product_in_stock = '#{qty}' WHERE virtuemart_product_id = 
    #{product['virtuemart_product_id']};")

                client.query("UPDATE jos_virtuemart_product_prices SET product_price = '#{cost}', product_currency = '#{currency}' WHERE virtuemart_product_id = '#{product['virtuemart_product_id']}';")
            else
                client.query("INSERT INTO jos_virtuemart_products( product_sku, product_name, product_s_desc, product_in_stock)  VALUES('#{d[:sku]}','#{d[:desc]}','#{d[:desc]}','#{d[:qty]}');")
                last_id = client.last_id

                client.query("INSERT INTO jos_virtuemart_product_prices(virtuemart_product_id, product_price, product_currency) VALUES('#{last_id}', '#{cost}', #{currency});")
            end
        end
    end

`query': Duplicate entry '' for key 3 (Mysql2::Error) on line 35:

client.query("INSERT INTO jos_virtuemart_products( product_sku, product_name, product_s_desc, product_in_stock)  VALUES('#{d[:sku]}','#{d[:desc]}','#{d[:desc]}','#{d[:qty]}');")
                last_id = client.last_id

tadman · Accepted Answer · 2011-06-09 20:12:47Z

1

Putting in raw SQL statements with arbitrary strings inlined like this is extremely dangerous. You absolutely must escape any values put into them for your application to work at all. The first description you get with an apostrophe will cause your SQL to fail.

In this case you would use client.quote on each and every one of the strings. No exceptions. You have probably seen tons of press about Sony getting hacked, and it's because of mistakes like this that serious breaches happen.

You should investigate using an ORM to help with this, even something as simple as Sequel or DataMapper, as they provide facilities to make this easy.

The reason you are getting a duplicate key is because you have a unique index on one of the columns you're inserting into, or one of the columns is not specified and has a default value that collides with an existing row.

answered Jun 9, 2011 at 20:12

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ingalcala Over a year ago

thank you tadman for your answer, there are two UNIQUE indexes in the table, and they are not the ones I am using to import data, one of them has autoincrement and the other one doesnt and it is not null. quote: one of the columns is not specified and has a default value that collides with an existing row. now how do I know that? I am using mysql administrator as a remote gui in my desktop.

Collectives™ on Stack Overflow

MySQL error: `query': Duplicate entry '' for key 3 (Mysql2::Error) on a Ruby file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related