A mini program to find published posts from a sql db. post_bad.rb doesn't use sql sanitization and is vulnerable to sql injection attacks, where a user can see unpublished posts.

post_bad.rb

require 'sqlite3'
require_relative 'post_db' # Setup db table

# Open a database
db = SQLite3::Database.new "test.db"

while true
  # Get book name
  puts "Find post by name:"
  post_name = gets.strip

  # Find published book
  query = "select * from posts where name='#{post_name}' AND published=1;"
  puts "Executing: #{query}"
  result = db.execute query

  # Show results
  puts "Result: #{result}"
end

post_db.rb

require 'sqlite3'

# Open a database
db = SQLite3::Database.new "test.db"

def destroy_table(db)
  db.execute "drop table posts;"
end

def create_table(db)
  db.execute "create table posts (name varchar(30), published int);"
end

def insert_data(db)
  db.execute "insert into posts VALUES ('published post 1', 1);"
  db.execute "insert into posts VALUES ('published post 2', 1);"
  db.execute "insert into posts VALUES ('unpublished post', 0);"
end

destroy_table(db)
create_table(db)
insert_data(db)

post_good.rb

require 'sqlite3'
require_relative 'post_db' # Setup db table

# Open a database
db = SQLite3::Database.new "test.db"

while true
  # Get post name
  puts "Find post by name:"
  post_name = gets.strip

  # Find published post
  query = "select * from posts where name=? AND published=1;"
  puts "Executing: #{query}"
  result = db.execute query, post_name

  # Show results
  puts "Result: #{result}"
end

# try entering unpublished post';)