Archive | SQL RSS feed for this section

Avoiding SQL injections using stored procedures and access privileges

8 Feb

In the previous article I showed that the only way to be sure no one messes with your query is to use prepared statements because the statement is precompiled and not alterable afterwards. For people who don’t like to use prepared statements due to the added complexity, or people who worry about the overhead of prepared statements for one time queries there is another solution: stored procedures.

Continue reading

INSERT … ON DUPLICATE KEY UPDATE and UNIQUE KEYs

8 Feb

A useful construct in MySQL when you want to create a new row in case it doesn’t exist yet or update an existing one is:

INSERT ... ON DUPLICATE KEY UPDATE

For example if you have a PRIMARY KEY ‘id’, a UNIQUE KEY ‘u_key’ and a value with no constraints ‘data’ and you want to insert or update ‘data’ referenced by ‘id’, you can write:

INSERT INTO my_table (id, data) VALUES (662606957, 'some data') ON DUPLICATE KEY UPDATE data='some data';

However, this only works as long as you don’t define the UNIQUE KEY as NOT NULL. If you do define it as NOT NULL, it won’t work because it can’t use the default value more than once. In that case you need to supply a unique value for the UNIQUE KEY as well:

INSERT INTO my_table (id, u_key, data) VALUES (662606957, '0x600613', 'some data') ON DUPLICATE KEY UPDATE u_key='0x600613', data='some data';

Avoiding SQL injections

3 Nov

Building (unsafe) queries

You would think that SQL injections are something of the past by now, as they are a well understood and easy to explain exploit. Though in reality a lot of existing and new code is still written without much thought of protecting against them. This is not so surprising as security practices are often presented as something extra rather than being strictly enforced from the start. Additionally web pages and books are seldom up to date given the fast changing nature of software development.
Continue reading