Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>TL;DR</h2> <pre class="lang-sql prettyprint-override"> -- The application accepts input, in this case 'Nancy', without attempting to -- sanitize the input, such as by escaping special characters school=> INSERT INTO students VALUES ('Nancy'); INSERT 0 1 -- SQL injection occurs when input into a database command is manipulated to -- cause the database server to execute arbitrary SQL school=> INSERT INTO students VALUES ('Robert'); DROP TABLE students; --'); INSERT 0 1 DROP TABLE -- The student records are now gone - it could have been even worse! school=> SELECT * FROM students; ERROR: relation "students" does not exist LINE 1: SELECT * FROM students; ^ </pre> <hr> <h3>This drops (deletes) the student table.</h3> <p>(<em>All code examples in this answer were run on a PostgreSQL 9.1.2 database server.</em>)</p> <p>To make it clear what's happening, let's try this with a simple table containing only the name field and add a single row:</p> <pre class="lang-sql prettyprint-override"> school=> CREATE TABLE students (name TEXT PRIMARY KEY); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "students_pkey" for table "students" CREATE TABLE school=> INSERT INTO students VALUES ('John'); INSERT 0 1 </pre> <p>Let's assume the application uses the following SQL to insert data into the table:</p> <pre class="lang-sql prettyprint-override"> INSERT INTO students VALUES ('foobar'); </pre> <p>Replace <code>foobar</code> with the actual name of the student. A normal insert operation would look like this:</p> <pre class="lang-sql prettyprint-override"> -- Input: Nancy school=> INSERT INTO students VALUES ('Nancy'); INSERT 0 1 </pre> <p>When we query the table, we get this:</p> <pre class="lang-sql prettyprint-override"> school=> SELECT * FROM students; name ------- John Nancy (2 rows) </pre> <p>What happens when we insert Little Bobby Tables's name into the table?</p> <pre class="lang-sql prettyprint-override"> -- Input: Robert'); DROP TABLE students; -- school=> INSERT INTO students VALUES ('Robert'); DROP TABLE students; --'); INSERT 0 1 DROP TABLE </pre> <p>The SQL injection here is the result of the name of the student terminating the statement and including a separate <code>DROP TABLE</code> command; the two dashes at the end of the input are intended to comment out any leftover code that would otherwise cause an error. The last line of the output confirms that the database server has dropped the table.</p> <p>It's important to notice that during the <code>INSERT</code> operation the application isn't checking the input for any special characters, and is therefore allowing arbitrary input to be entered into the SQL command. This means that a malicious user can insert, into a field normally intended for user input, special symbols such as quotes along with arbitrary SQL code to cause the database system to execute it, hence <a href="http://en.wikipedia.org/wiki/SQL_injection" rel="nofollow noreferrer">SQL&nbsp;<em>injection</em></a>.</p> <p>The result?</p> <pre class="lang-sql prettyprint-override"> school=> SELECT * FROM students; ERROR: relation "students" does not exist LINE 1: SELECT * FROM students; ^ </pre> <p>SQL injection is the database equivalent of a remote <a href="http://en.wikipedia.org/wiki/Arbitrary_code_execution" rel="nofollow noreferrer">arbitrary code execution</a> vulnerability in an operating system or application. The potential impact of a successful SQL injection attack cannot be underestimated--depending on the database system and application configuration, it can be used by an attacker to cause data loss (as in this case), gain unauthorized access to data, or even execute arbitrary code on the host machine itself.</p> <p>As noted by the XKCD comic, one way of protecting against SQL injection attacks is to sanitize database inputs, such as by escaping special characters, so that they cannot modify the underlying SQL command and therefore cannot cause execution of arbitrary SQL code. If you use parameterized queries, such as by using <a href="http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx" rel="nofollow noreferrer"><code>SqlParameter</code></a> in ADO.NET, the input will, at minimum, be automatically sanitized to guard against SQL injection.</p> <p>However, sanitizing inputs at the application level may not stop more advanced SQL injection techniques. For example, <a href="https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string">there are ways to circumvent the <code>mysql_real_escape_string</code> PHP function</a>. For added protection, many database systems support <a href="https://en.wikipedia.org/wiki/Prepared_statement" rel="nofollow noreferrer"><em>prepared statements</em></a>. If properly implemented in the backend, prepared statements can make SQL injection impossible by treating data inputs as semantically separate from the rest of the command.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow does the SQL injection from the "Bobby Tables" XKCD comic work?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USbwDraco
    1. USbwDraco
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload