I was describing SQL injection to some trainees today and couldn’t find any simple examples which used a variable to replicate a “slightly” realistic scenario (those quotes can be tricky!). So, here are some basic ones. Uncomment @val as necessary!

use personDb;

-- set @val = 'john';
-- set @val = 'john"; -- '; -- remove any further criteria checking
-- set @val = 'john" or id = 3 -- '; -- guess column names, error means column doesn't exist
-- set @val = 'john" or "1" = "1'; -- get all records

set @dynamic_sql=CONCAT('select * from person where name = "', @val, '"');

-- select @dynamic_sql;

PREPARE statement FROM @dynamic_sql; 
EXECUTE statement; 
DEALLOCATE PREPARE statement;

As I’m using a prepared statement here, it’s not possible to end the statement and do other more serious operations like delete from person; but still enough to do some damange!