• Community
    • Join Discord
    • Industry News
    • Discussions
    • Contact & Support
  • Learn
    • Categories
      • Cybersecurity Fundamentals
      • Networking Security
      • Application Security
      • Ethical Hacking
      • Cloud Security
    • Featured Courses
    • All Courses
  • Resources
    • Blog
    • Ebooks
    • Podcasts
    • Webinars
  • Pricing
  • About Cybr
    Sign in Sign up

    Cybr logo badge

    Cybr logo badge

    • Community
      • Join Discord
      • Industry News
      • Discussions
      • Contact & Support
    • Learn
      • Categories
        • Cybersecurity Fundamentals
        • Networking Security
        • Application Security
        • Ethical Hacking
        • Cloud Security
      • Featured Courses
      • All Courses
    • Resources
      • Blog
      • Ebooks
      • Podcasts
      • Webinars
    • Pricing
    • About Cybr
    Sign in Sign up

    Tag: Databases

    SQL primer for SQL injections cover

    A quick SQL primer for SQL injections

    While there are many other tutorials and resources out there that cover SQL, I wrote this blog post to give you a quick SQL primer…

    Christophe August 13, 2020
    0 Comments

    Get notified of new posts

    Recent Posts

    • Biometrics techniques and concepts for authentication
    • Key stretching concepts and algorithms
    • Hashing use cases
    • Hash Tables, Rainbow Table Attacks, and Salts
    • Useful IP widget for CTFs

    Categories

    • Announcements
    • Application Security
    • Beginner
    • Career
    • Certifications
    • Community
    • Cybersecurity
    • Cybersecurity Fundamentals
    • Ethical Hacking
    • Incident Response
    • Intermediate
    • Network Security
    • Programming Languages
    • Scripting

    Recent Comments

    • Christophe on Lightweight directory access protocol (LDAP) Injections
    • dosmendoza on Lightweight directory access protocol (LDAP) Injections
    • Christophe on Continue building sqlmap skills
    • king07 on Continue building sqlmap skills
    • Christophe on XSS Hunter
    Cybr logo badge
    © 2023 - Cybr, Inc
    • Contact
    • Teach at Cybr
    • Courses
    • Forums
    • Terms of Service
    • Privacy Policy

    Forum Description

    While there are many other tutorials and resources out there that cover SQL, I wrote this blog post to give you a quick SQL primer for SQL injections, because there are a few facts that will help make that learning journey easier.

    So this is not meant as a complete explanation of SQL. If you have zero knowledge of SQL, you should probably take a proper course for that first. Think of this as a crash course on SQL in just a few minutes in case you haven’t touched it in a while.

    What is SQL?

    SQL is a language designed for communicating with databases that store our data. It allows us to read, write, and edit data, and it also allows us to configure or manage the database engine being used for our application(s).

    This makes SQL a very powerful tool because it's not just something database administrators use. Developers may need to build their own databases or tables and will more likely than not also need to know how to manipulate data in their databases.

    Beyond being a helpful tool to build applications, it can also be a tool used against those very same applications. While we won't introduce SQL injections in this post, but instead in another one, many of the concepts explained here directly translate to performing SQL injections.

    Setting up an example database

    An SQL primer for SQL injections wouldn't be complete without some hands-on practice! I’ll be using a website called SQLFiddle in order to demonstrate, and this is a free resource so feel free to follow along.

    So let’s say that we have two tables in our database:

    1. Users
    2. Products

    Here’s the SQL I used to build the schema in my SQLFiddle example:

    CREATE TABLE Users (
        ID int,
        Email varchar(255),
        Password varchar(255),
        RegistrationDate varchar(255),
        PhysicalAddress varchar(255)
    );
    
    CREATE TABLE Products (
        ID int,
        ProductName varchar(255),
        ProductDescription varchar(255),
        ProductPrice varchar(255),
        Quantity varchar(255)
    );
    
    INSERT INTO Users (ID, Email, Password, RegistrationDate, PhysicalAddress)
    VALUES (159, 'Tom@gmail.com', 'strongpassword12345', '7-1-2020', 'Stavanger, 4006 Norway'),
    (11, 'Jon@gmail.com', 'b3stp@assw0rd', '5-5-2019', 'SQLFiddle, 4006 USA');
    
    INSERT INTO Products (ID, ProductName, ProductDescription, ProductPrice, Quantity)
    VALUES (13, 'Cybr SQL Injection T-Shirt', 'Really cool t-shirt for the first 15 people who complete this course!', '$0', '10'),
    (346, 'Vaccuum', 'The most powerful battery-powered vaccuum cleaner', '$499.99', '44');

    If you're following along in SQLFiddle, make sure to click on "Build Schema" after copy/pasting the above!

    The Users table contains information about all the users of your application including:

    • ID
    • Email
    • Password
    • RegistrationDate
    • PhysicalAddress

    And the Products table contains information about all the products in your online store including:

    • ID
    • ProductName
    • ProductDescription
    • ProductPrice
    • Quantity

    Examples of SQL queries

    To pull data from the Users table, we might run this query:

    SELECT * FROM Users;

    And it will pull all columns from Users.

    SQL Select statement

    Or for just the registration date of a specific user, we could run:

    SELECT RegistrationDate FROM Users WHERE Email ='abc@cybr.com';

    In order to run these queries, in some cases, we have to use information submitted by our users or our application.

    For example, if a user is checking out our product with ID 346, our application may request it like this:

    const http = new XMLHttpRequest()
    
    http.open("GET", "https://url.co/v1/products/346")
    http.send()
    
    http.onload = () => console.log(http.responseText)

    and that API URL is running this SQL query:

    SELECT * FROM Products WHERE ID='346';
    Select a specific ID

    We could also run UPDATE statements, to update our data.

    UPDATE Products SET ProductPrice='0' WHERE id='346';

    SQLFiddle doesn’t allow us to UPDATE tables from this window, but this would work in practice.

    We can use UNION statements to combine results from multiple SELECT statements into a single result set:

    SELECT Email,Password FROM Users UNION SELECT ProductName, ProductPrice from Products;

    We can DROP tables or even entire databases:

    DROP TABLE Users;

    and a whole lot more…

    Database Metadata Tables - Helpful info for SQL Injections

    The database engines also typically have tables that contain general information and metadata, such as keeping track of all tables or the database’s schema — basically, the essentials for the database to keep running properly.

    For different database engines, it’s a bit different:

    • SQLite → sqlite_master
    • MySQL → information_schema
    • PostgreSQL → information_schema
    • Oracle → dba_tables
    Database schema table names


    For example, the sqlite_master table looks like this:

    Column NameDescription
    typeThe type of database object such as table, index, trigger, or view
    nameName of the database object
    tbl_nameThe table name that the database object is associated with
    rootpageThe root page (for B-trees)
    sqlSQL used to create the database object
    sqlite_master table schema


    So to get a complete list of all tables in a database for SQLite, we could run this query:

    SELECT name FROM sqlite_master
    WHERE type='table'ORDER BY name;

    For MySQL we could use something like this:

    SELECT table_name from information_schema.tables
    WHERE table_type = 'BASE TABLE' and table_schema = database();

    While for PostgreSQL it could just be 

    SELECT * FROM information_schema.tables;

    And in Oracle:

    SELECT table_name FROM dba_tables;

    Concluding our quick SQL primer for SQL injections

    All of what we talked about will be important as we perform SQL injections, because what allows our applications to manipulate database data can also be used to exploit those very same databases. 

    That’s right, seemingly harmless queries can result in simple — all the way to massive — exploits that take down applications, or extract sensitive information.

    As we complete this blog post, go ahead and move on to the next in order to learn the concepts of SQL injections.

    Report

    There was a problem reporting this post.

    Harassment or bullying behavior
    Contains mature or sensitive content
    Contains misleading or false information
    Contains abusive or derogatory content
    Contains spam, fake content or potential malware

    Block Member?

    Please confirm you want to block this member.

    You will no longer be able to:

    • See blocked member's posts
    • Mention this member in posts

    Please allow a few minutes for this process to complete.

    Report

    You have already reported this .