How does the SQL injection from the Bobby Tables XKCD comic work

Question

Just looking at     Source  https   xkcd com 327    What does this SQL do   Robert    DROP TABLE STUDENTS  --   I know both   and -- are for comments  but doesn t the word DROP get commented as well since it is part of the same line

User · Answer

The   character in SQL is used  for string constants  In this case it is used for ending the string constant and not for comment

User · Answer

It drops the students table   The original code in the school s program probably looks something like  q    INSERT INTO Students VALUES       FNMName Text            LName Text           This is the naive way to add text input into a query  and is very bad  as you will see   After the values from the first name  middle name textbox FNMName Text  which is Robert    DROP TABLE STUDENTS  --  and the last name textbox LName Text  let s call it Derper  are concatenated with the rest of the query  the result is now actually two queries separated by the statement terminator  semicolon    The second query has been injected into the first   When the code executes this query against the database  it will look like this  INSERT INTO Students VALUES   Robert    DROP TABLE Students  --    Derper     which  in plain English  roughly translates to the two queries      Add a new record to the Students table with a Name value of  Robert    and     Delete the Students table   Everything past the second query is marked as a comment   --    Derper    The   in the student s name is not a comment  it s the closing string delimiter   Since the student s name is a string  it s needed syntactically to complete the hypothetical query   Injection attacks only work when the SQL query they inject results in valid SQL   Edited again as per dan04 s astute comment

User · Answer

This is how it works  Lets suppose the administrator is looking for records of student    Robert    DROP TABLE STUDENTS  --   Since the admin account has high privileges deleting the table from this account is possible   The code to retrieve user name from request is  Now the query would be something like this  to search the student table   String query  Select   from student where username    student name       statement executeQuery query     Rest of the code follows   The resultant query becomes  Select   from student where username  Robert    DROP TABLE STUDENTS  --   Since the user input is not sanitized  The above query has is manipulated into 2 parts  Select   from student where username  Robert      DROP TABLE STUDENTS  --   The double dash  --  will just comment out remaining part of the query    This is dangerous as it can nullify password authentication  if present  The first one will do the normal search    The second one will drop the table student if the account has sufficient privileges  Generally the school admin account will run such query and will have the privileges talked about above

User · Answer

Say you naively wrote a student creation method like this   void createStudent String name        database execute  INSERT INTO students  name  VALUES       name              And someone enters the name Robert    DROP TABLE STUDENTS  --  What gets run on the database is this query   INSERT INTO students  name  VALUES   Robert    DROP TABLE STUDENTS --     The semicolon ends the insert command and starts another  the -- comments out the rest of the line  The DROP TABLE command is executed     This is why bind parameters are a good thing

User · Answer

The     ends the query  it doesn t start a comment  Then it drops the students table and comments the rest of the query that was supposed to be executed

User · Answer

The   character in SQL is used  for string constants  In this case it is used for ending the string constant and not for comment

User · Answer

It drops the students table   The original code in the school s program probably looks something like  q    INSERT INTO Students VALUES       FNMName Text            LName Text           This is the naive way to add text input into a query  and is very bad  as you will see   After the values from the first name  middle name textbox FNMName Text  which is Robert    DROP TABLE STUDENTS  --  and the last name textbox LName Text  let s call it Derper  are concatenated with the rest of the query  the result is now actually two queries separated by the statement terminator  semicolon    The second query has been injected into the first   When the code executes this query against the database  it will look like this  INSERT INTO Students VALUES   Robert    DROP TABLE Students  --    Derper     which  in plain English  roughly translates to the two queries      Add a new record to the Students table with a Name value of  Robert    and     Delete the Students table   Everything past the second query is marked as a comment   --    Derper    The   in the student s name is not a comment  it s the closing string delimiter   Since the student s name is a string  it s needed syntactically to complete the hypothetical query   Injection attacks only work when the SQL query they inject results in valid SQL   Edited again as per dan04 s astute comment

User · Answer

A single quote is the start and end of a string  A semicolon is the end of a statement  So if they were doing a select like this   Select   From Students Where  Name     lt NameGetsInsertedHere gt      The SQL would become   Select   From Students Where  Name    Robert    DROP TABLE STUDENTS  --   --              -------------------------------    On some systems  the select would get ran first followed by the drop statement  The message is  DONT EMBED VALUES INTO YOUR SQL  Instead use parameters

User · Answer

The     ends the query  it doesn t start a comment  Then it drops the students table and comments the rest of the query that was supposed to be executed

User · Answer

In this case    is not a comment character   It s used to delimit string literals   The comic artist is banking on the idea that the school in question has dynamic sql somewhere that looks something like this    sql    INSERT INTO  Students   FirstName  LastName  VALUES        fname             lname           So now the   character ends the string literal before the programmer was expecting it   Combined with the   character to end the statement  an attacker can now add whatever sql they want   The -- comment at the end is to make sure any remaining sql in the original statement does not prevent the query from compiling on the server   FWIW  I also think the comic in question has an important detail wrong  if you re thinking about sanitizing your database inputs  as the comic suggests  you re still doing it wrong  Instead  you should think in terms of quarantining your database inputs  and the correct way to do this is via parameterized queries

User · Answer

It drops the students table   The original code in the school s program probably looks something like  q    INSERT INTO Students VALUES       FNMName Text            LName Text           This is the naive way to add text input into a query  and is very bad  as you will see   After the values from the first name  middle name textbox FNMName Text  which is Robert    DROP TABLE STUDENTS  --  and the last name textbox LName Text  let s call it Derper  are concatenated with the rest of the query  the result is now actually two queries separated by the statement terminator  semicolon    The second query has been injected into the first   When the code executes this query against the database  it will look like this  INSERT INTO Students VALUES   Robert    DROP TABLE Students  --    Derper     which  in plain English  roughly translates to the two queries      Add a new record to the Students table with a Name value of  Robert    and     Delete the Students table   Everything past the second query is marked as a comment   --    Derper    The   in the student s name is not a comment  it s the closing string delimiter   Since the student s name is a string  it s needed syntactically to complete the hypothetical query   Injection attacks only work when the SQL query they inject results in valid SQL   Edited again as per dan04 s astute comment

User · Answer

No    isn t a comment in SQL  but a delimiter   Mom supposed the database programmer made a request looking like   INSERT INTO  students    first name    last name   VALUES    firstName     lastName       for example  to add the new student  where the  xxx variable contents was taken directly out of an HTML form  without checking format nor escaping special characters   So if  firstName contains Robert    DROP TABLE students  -- the database program will execute the following request directly on the DB    INSERT INTO  students    first name    last name   VALUES   Robert    DROP TABLE students  --    XKCD      ie  it will terminate early the insert statement  execute whatever malicious code the cracker wants  then comment out whatever remainder of code there might be   Mmm  I am too slow  I see already 8 answers before mine in the orange band     -  A popular topic  it seems

User · Answer

The writer of the database probably did a   sql    SELECT   FROM STUDENTS WHERE  STUDENT NAME        student name       AND other stuff   execute sql     If student name is the one given  that does the selection with the name  Robert  and then drops the table   The  --   part changes the rest of the given query into a comment

User · Answer

In this case    is not a comment character   It s used to delimit string literals   The comic artist is banking on the idea that the school in question has dynamic sql somewhere that looks something like this    sql    INSERT INTO  Students   FirstName  LastName  VALUES        fname             lname           So now the   character ends the string literal before the programmer was expecting it   Combined with the   character to end the statement  an attacker can now add whatever sql they want   The -- comment at the end is to make sure any remaining sql in the original statement does not prevent the query from compiling on the server   FWIW  I also think the comic in question has an important detail wrong  if you re thinking about sanitizing your database inputs  as the comic suggests  you re still doing it wrong  Instead  you should think in terms of quarantining your database inputs  and the correct way to do this is via parameterized queries

User · Answer

TL DR  -- 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                            This drops  deletes  the student table   All code examples in this answer were run on a PostgreSQL 9 1 2 database server   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   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  Let s assume the application uses the following SQL to insert data into the table   INSERT INTO students VALUES   foobar     Replace foobar with the actual name of the student  A normal insert operation would look like this   --                            Input    Nancy school   INSERT INTO students VALUES   Nancy    INSERT 0 1  When we query the table  we get this   school   SELECT   FROM students   name -------  John  Nancy  2 rows   What happens when we insert Little Bobby Tables s name into the table   --                            Input    Robert    DROP TABLE students  -- school   INSERT INTO students VALUES   Robert    DROP TABLE students  --    INSERT 0 1 DROP TABLE  The SQL injection here is the result of the name of the student terminating the statement and including a separate DROP TABLE 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  It s important to notice that during the INSERT 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 SQL  injection  The result   school   SELECT   FROM students  ERROR   relation  students  does not exist LINE 1  SELECT   FROM students                           SQL injection is the database equivalent of a remote arbitrary code execution 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  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 SqlParameter in ADO NET  the input will  at minimum  be automatically sanitized to guard against SQL injection  However  sanitizing inputs at the application level may not stop more advanced SQL injection techniques  For example  there are ways to circumvent the mysql real escape string PHP function  For added protection  many database systems support prepared statements  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

User · Answer

No    isn t a comment in SQL  but a delimiter   Mom supposed the database programmer made a request looking like   INSERT INTO  students    first name    last name   VALUES    firstName     lastName       for example  to add the new student  where the  xxx variable contents was taken directly out of an HTML form  without checking format nor escaping special characters   So if  firstName contains Robert    DROP TABLE students  -- the database program will execute the following request directly on the DB    INSERT INTO  students    first name    last name   VALUES   Robert    DROP TABLE students  --    XKCD      ie  it will terminate early the insert statement  execute whatever malicious code the cracker wants  then comment out whatever remainder of code there might be   Mmm  I am too slow  I see already 8 answers before mine in the orange band     -  A popular topic  it seems

User · Answer

You don t need to input form data to make SQL injection    No one pointed this out before so through I might alert some of you    Mostly we will try to patch forms input  But this is not the only place where you can get attacked with SQL injection  You can do very simple attack with URL which send data through GET request  Consider the fallowing example    lt a href   show id 1  gt show something lt  a gt    Your url would look  http   yoursite com show id 1  Now someone could try something like this   http   yoursite com show id 1 TRUNCATE table name   Try to replace table name with the real table name  If he get your table name right they would empty your table   It is very easy to brut force this URL with simple script   Your query would look something like this      SELECT   FROM page WHERE id   4 TRUNCATE page    Example of PHP vulnerable code using PDO    lt  php      id     GET  id      pdo   new PDO  database dsn   database user   database pass    query    SELECT   FROM page WHERE id     id     stmt    pdo- gt query  query    data    stmt- gt fetch                    You have lost your data                            Solution - use PDO prepare     bindParam   methods    lt  php      id     GET  id      query    SELECT   FROM page WHERE id    idVal    stmt    pdo- gt prepare  query    stmt- gt bindParam  idVal    id  PDO  PARAM INT    stmt- gt execute     data    stmt- gt fetch                   Your data is safe

User · Answer

No    isn t a comment in SQL  but a delimiter   Mom supposed the database programmer made a request looking like   INSERT INTO  students    first name    last name   VALUES    firstName     lastName       for example  to add the new student  where the  xxx variable contents was taken directly out of an HTML form  without checking format nor escaping special characters   So if  firstName contains Robert    DROP TABLE students  -- the database program will execute the following request directly on the DB    INSERT INTO  students    first name    last name   VALUES   Robert    DROP TABLE students  --    XKCD      ie  it will terminate early the insert statement  execute whatever malicious code the cracker wants  then comment out whatever remainder of code there might be   Mmm  I am too slow  I see already 8 answers before mine in the orange band     -  A popular topic  it seems

User · Answer

You don t need to input form data to make SQL injection    No one pointed this out before so through I might alert some of you    Mostly we will try to patch forms input  But this is not the only place where you can get attacked with SQL injection  You can do very simple attack with URL which send data through GET request  Consider the fallowing example    lt a href   show id 1  gt show something lt  a gt    Your url would look  http   yoursite com show id 1  Now someone could try something like this   http   yoursite com show id 1 TRUNCATE table name   Try to replace table name with the real table name  If he get your table name right they would empty your table   It is very easy to brut force this URL with simple script   Your query would look something like this      SELECT   FROM page WHERE id   4 TRUNCATE page    Example of PHP vulnerable code using PDO    lt  php      id     GET  id      pdo   new PDO  database dsn   database user   database pass    query    SELECT   FROM page WHERE id     id     stmt    pdo- gt query  query    data    stmt- gt fetch                    You have lost your data                            Solution - use PDO prepare     bindParam   methods    lt  php      id     GET  id      query    SELECT   FROM page WHERE id    idVal    stmt    pdo- gt prepare  query    stmt- gt bindParam  idVal    id  PDO  PARAM INT    stmt- gt execute     data    stmt- gt fetch                   Your data is safe

User · Answer

The writer of the database probably did a   sql    SELECT   FROM STUDENTS WHERE  STUDENT NAME        student name       AND other stuff   execute sql     If student name is the one given  that does the selection with the name  Robert  and then drops the table   The  --   part changes the rest of the given query into a comment

User · Answer

The     ends the query  it doesn t start a comment  Then it drops the students table and comments the rest of the query that was supposed to be executed

User · Answer

Let s say the name was used in a variable   Name      You then run this query   INSERT INTO Students VALUES     Name      The code is mistakenly placing anything the user supplied as the variable   You wanted the SQL to be      INSERT INTO Students VALUES    Robert Tables      But a clever user can supply whatever they want      INSERT INTO Students VALUES    Robert    DROP TABLE Students  --       What you get is   INSERT INTO Students VALUES    Robert      DROP TABLE STUDENTS  --      The -- only comments the remainder of the line

User · Answer

The writer of the database probably did a   sql    SELECT   FROM STUDENTS WHERE  STUDENT NAME        student name       AND other stuff   execute sql     If student name is the one given  that does the selection with the name  Robert  and then drops the table   The  --   part changes the rest of the given query into a comment

User · Answer

This is how it works  Lets suppose the administrator is looking for records of student    Robert    DROP TABLE STUDENTS  --   Since the admin account has high privileges deleting the table from this account is possible   The code to retrieve user name from request is  Now the query would be something like this  to search the student table   String query  Select   from student where username    student name       statement executeQuery query     Rest of the code follows   The resultant query becomes  Select   from student where username  Robert    DROP TABLE STUDENTS  --   Since the user input is not sanitized  The above query has is manipulated into 2 parts  Select   from student where username  Robert      DROP TABLE STUDENTS  --   The double dash  --  will just comment out remaining part of the query    This is dangerous as it can nullify password authentication  if present  The first one will do the normal search    The second one will drop the table student if the account has sufficient privileges  Generally the school admin account will run such query and will have the privileges talked about above

User · Answer

In this case    is not a comment character   It s used to delimit string literals   The comic artist is banking on the idea that the school in question has dynamic sql somewhere that looks something like this    sql    INSERT INTO  Students   FirstName  LastName  VALUES        fname             lname           So now the   character ends the string literal before the programmer was expecting it   Combined with the   character to end the statement  an attacker can now add whatever sql they want   The -- comment at the end is to make sure any remaining sql in the original statement does not prevent the query from compiling on the server   FWIW  I also think the comic in question has an important detail wrong  if you re thinking about sanitizing your database inputs  as the comic suggests  you re still doing it wrong  Instead  you should think in terms of quarantining your database inputs  and the correct way to do this is via parameterized queries

User · Answer

A single quote is the start and end of a string  A semicolon is the end of a statement  So if they were doing a select like this   Select   From Students Where  Name     lt NameGetsInsertedHere gt      The SQL would become   Select   From Students Where  Name    Robert    DROP TABLE STUDENTS  --   --              -------------------------------    On some systems  the select would get ran first followed by the drop statement  The message is  DONT EMBED VALUES INTO YOUR SQL  Instead use parameters

User · Answer

As everyone else has pointed out already  the     closes the original statement and then a second statement follows  Most frameworks  including languages like PHP  have default security settings by now that don t allow multiple statements in one SQL string  In PHP  for example  you can only run multiple statements in one SQL string by using the mysqli multi query function   You can  however  manipulate an existing SQL statement via SQL injection without having to add a second statement  Let s say you have a login system which checks a username and a password with this simple select    query  SELECT   FROM users WHERE username        REQUEST  user        and  password      REQUEST  pass          result mysql query  query     If you provide peter as the username and secret as the password  the resulting SQL string would look like this   SELECT   FROM users WHERE username  peter  and  password  secret     Everything s fine  Now imagine you provide this string as the password     OR  1   1   Then the resulting SQL string would be this   SELECT   FROM users WHERE username  peter  and  password    OR  1   1     That would enable you to log in to any account without knowing the password  So you don t need to be able to use two statements in order to use SQL injection  although you can do more destructive things if you are able to supply multiple statements

User · Answer

In this case    is not a comment character   It s used to delimit string literals   The comic artist is banking on the idea that the school in question has dynamic sql somewhere that looks something like this    sql    INSERT INTO  Students   FirstName  LastName  VALUES        fname             lname           So now the   character ends the string literal before the programmer was expecting it   Combined with the   character to end the statement  an attacker can now add whatever sql they want   The -- comment at the end is to make sure any remaining sql in the original statement does not prevent the query from compiling on the server   FWIW  I also think the comic in question has an important detail wrong  if you re thinking about sanitizing your database inputs  as the comic suggests  you re still doing it wrong  Instead  you should think in terms of quarantining your database inputs  and the correct way to do this is via parameterized queries

User · Answer

Say you naively wrote a student creation method like this   void createStudent String name        database execute  INSERT INTO students  name  VALUES       name              And someone enters the name Robert    DROP TABLE STUDENTS  --  What gets run on the database is this query   INSERT INTO students  name  VALUES   Robert    DROP TABLE STUDENTS --     The semicolon ends the insert command and starts another  the -- comments out the rest of the line  The DROP TABLE command is executed     This is why bind parameters are a good thing

User · Answer

The writer of the database probably did a   sql    SELECT   FROM STUDENTS WHERE  STUDENT NAME        student name       AND other stuff   execute sql     If student name is the one given  that does the selection with the name  Robert  and then drops the table   The  --   part changes the rest of the given query into a comment

User · Answer

Say you naively wrote a student creation method like this   void createStudent String name        database execute  INSERT INTO students  name  VALUES       name              And someone enters the name Robert    DROP TABLE STUDENTS  --  What gets run on the database is this query   INSERT INTO students  name  VALUES   Robert    DROP TABLE STUDENTS --     The semicolon ends the insert command and starts another  the -- comments out the rest of the line  The DROP TABLE command is executed     This is why bind parameters are a good thing

User · Answer

The     ends the query  it doesn t start a comment  Then it drops the students table and comments the rest of the query that was supposed to be executed

User · Answer

The   character in SQL is used  for string constants  In this case it is used for ending the string constant and not for comment

User · Answer

Let s say the name was used in a variable   Name      You then run this query   INSERT INTO Students VALUES     Name      The code is mistakenly placing anything the user supplied as the variable   You wanted the SQL to be      INSERT INTO Students VALUES    Robert Tables      But a clever user can supply whatever they want      INSERT INTO Students VALUES    Robert    DROP TABLE Students  --       What you get is   INSERT INTO Students VALUES    Robert      DROP TABLE STUDENTS  --      The -- only comments the remainder of the line

User · Answer

Let s say the name was used in a variable   Name      You then run this query   INSERT INTO Students VALUES     Name      The code is mistakenly placing anything the user supplied as the variable   You wanted the SQL to be      INSERT INTO Students VALUES    Robert Tables      But a clever user can supply whatever they want      INSERT INTO Students VALUES    Robert    DROP TABLE Students  --       What you get is   INSERT INTO Students VALUES    Robert      DROP TABLE STUDENTS  --      The -- only comments the remainder of the line

User · Answer

No    isn t a comment in SQL  but a delimiter   Mom supposed the database programmer made a request looking like   INSERT INTO  students    first name    last name   VALUES    firstName     lastName       for example  to add the new student  where the  xxx variable contents was taken directly out of an HTML form  without checking format nor escaping special characters   So if  firstName contains Robert    DROP TABLE students  -- the database program will execute the following request directly on the DB    INSERT INTO  students    first name    last name   VALUES   Robert    DROP TABLE students  --    XKCD      ie  it will terminate early the insert statement  execute whatever malicious code the cracker wants  then comment out whatever remainder of code there might be   Mmm  I am too slow  I see already 8 answers before mine in the orange band     -  A popular topic  it seems

User · Answer

A single quote is the start and end of a string  A semicolon is the end of a statement  So if they were doing a select like this   Select   From Students Where  Name     lt NameGetsInsertedHere gt      The SQL would become   Select   From Students Where  Name    Robert    DROP TABLE STUDENTS  --   --              -------------------------------    On some systems  the select would get ran first followed by the drop statement  The message is  DONT EMBED VALUES INTO YOUR SQL  Instead use parameters

User · Answer

The   character in SQL is used  for string constants  In this case it is used for ending the string constant and not for comment

User · Answer

As everyone else has pointed out already  the     closes the original statement and then a second statement follows  Most frameworks  including languages like PHP  have default security settings by now that don t allow multiple statements in one SQL string  In PHP  for example  you can only run multiple statements in one SQL string by using the mysqli multi query function   You can  however  manipulate an existing SQL statement via SQL injection without having to add a second statement  Let s say you have a login system which checks a username and a password with this simple select    query  SELECT   FROM users WHERE username        REQUEST  user        and  password      REQUEST  pass          result mysql query  query     If you provide peter as the username and secret as the password  the resulting SQL string would look like this   SELECT   FROM users WHERE username  peter  and  password  secret     Everything s fine  Now imagine you provide this string as the password     OR  1   1   Then the resulting SQL string would be this   SELECT   FROM users WHERE username  peter  and  password    OR  1   1     That would enable you to log in to any account without knowing the password  So you don t need to be able to use two statements in order to use SQL injection  although you can do more destructive things if you are able to supply multiple statements

User · Answer

TL DR  -- 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                            This drops  deletes  the student table   All code examples in this answer were run on a PostgreSQL 9 1 2 database server   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   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  Let s assume the application uses the following SQL to insert data into the table   INSERT INTO students VALUES   foobar     Replace foobar with the actual name of the student  A normal insert operation would look like this   --                            Input    Nancy school   INSERT INTO students VALUES   Nancy    INSERT 0 1  When we query the table  we get this   school   SELECT   FROM students   name -------  John  Nancy  2 rows   What happens when we insert Little Bobby Tables s name into the table   --                            Input    Robert    DROP TABLE students  -- school   INSERT INTO students VALUES   Robert    DROP TABLE students  --    INSERT 0 1 DROP TABLE  The SQL injection here is the result of the name of the student terminating the statement and including a separate DROP TABLE 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  It s important to notice that during the INSERT 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 SQL  injection  The result   school   SELECT   FROM students  ERROR   relation  students  does not exist LINE 1  SELECT   FROM students                           SQL injection is the database equivalent of a remote arbitrary code execution 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  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 SqlParameter in ADO NET  the input will  at minimum  be automatically sanitized to guard against SQL injection  However  sanitizing inputs at the application level may not stop more advanced SQL injection techniques  For example  there are ways to circumvent the mysql real escape string PHP function  For added protection  many database systems support prepared statements  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

User · Answer

A single quote is the start and end of a string  A semicolon is the end of a statement  So if they were doing a select like this   Select   From Students Where  Name     lt NameGetsInsertedHere gt      The SQL would become   Select   From Students Where  Name    Robert    DROP TABLE STUDENTS  --   --              -------------------------------    On some systems  the select would get ran first followed by the drop statement  The message is  DONT EMBED VALUES INTO YOUR SQL  Instead use parameters

User · Answer

It drops the students table   The original code in the school s program probably looks something like  q    INSERT INTO Students VALUES       FNMName Text            LName Text           This is the naive way to add text input into a query  and is very bad  as you will see   After the values from the first name  middle name textbox FNMName Text  which is Robert    DROP TABLE STUDENTS  --  and the last name textbox LName Text  let s call it Derper  are concatenated with the rest of the query  the result is now actually two queries separated by the statement terminator  semicolon    The second query has been injected into the first   When the code executes this query against the database  it will look like this  INSERT INTO Students VALUES   Robert    DROP TABLE Students  --    Derper     which  in plain English  roughly translates to the two queries      Add a new record to the Students table with a Name value of  Robert    and     Delete the Students table   Everything past the second query is marked as a comment   --    Derper    The   in the student s name is not a comment  it s the closing string delimiter   Since the student s name is a string  it s needed syntactically to complete the hypothetical query   Injection attacks only work when the SQL query they inject results in valid SQL   Edited again as per dan04 s astute comment

User · Answer

Let s say the name was used in a variable   Name      You then run this query   INSERT INTO Students VALUES     Name      The code is mistakenly placing anything the user supplied as the variable   You wanted the SQL to be      INSERT INTO Students VALUES    Robert Tables      But a clever user can supply whatever they want      INSERT INTO Students VALUES    Robert    DROP TABLE Students  --       What you get is   INSERT INTO Students VALUES    Robert      DROP TABLE STUDENTS  --      The -- only comments the remainder of the line

User · Answer

Say you naively wrote a student creation method like this   void createStudent String name        database execute  INSERT INTO students  name  VALUES       name              And someone enters the name Robert    DROP TABLE STUDENTS  --  What gets run on the database is this query   INSERT INTO students  name  VALUES   Robert    DROP TABLE STUDENTS --     The semicolon ends the insert command and starts another  the -- comments out the rest of the line  The DROP TABLE command is executed     This is why bind parameters are a good thing

[security] How does the SQL injection from the "Bobby Tables" XKCD comic work?

Examples related to security

Examples related to validation

Examples related to sql-injection