SQL INSERT Queries

**chili5** · 09-03-2009, 06:55 AM

SQL INSERT Queries

We use SQL insert queries to add data into tables. There are two forms of this command. One where we specify the columns we want to insert into and the other where we don't specify what columns to insert into.

If we don't specify what columns to insert into, we are inserting into all columns. I like to always specify what columns I am using. Note, if you specify four columns to insert into, but only provide 3 values then you will get an error.

Let us use this table here to demonstrate insert queries:

Code:

CREATE TABLE IF NOT EXISTS `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

All columns

The generic syntax for inserting into all columns is:

Code:

INSERT INTO table VALUES('one','two','three'....)

Integer values aren't in single quotes but everything else should be in single quotes.

Example:

Code:

INSERT INTO people VALUES(7,'joey','2009-04-07');

This is going to insert one record into the table. The id number is 7, the name is 'joey' and the date is 2009-04-07.

Specifying the Columns

One reason, we might want to do this is so we can use an auto increment field that populates itself.

Example:

Code:

INSERT INTO people (name,date) VALUES('black','2009-04-22')

After we name the table in parenthesis we specify the name of the columns we want to insert into. They are not in quotes. They are separated by a comma also. Then we specify the values for each column. Since we specified to use 2 columns we must specify 2 values to use.

The id field is auto incremented so it will populate itself.

Incorrect Number of Values

If we specify 2 columns and provide more or less than 2 values then we will see an error.

Example:

Code:

INSERT INTO people (name) VALUES('black','2009-04-22')

Output:

#1136 - Column count doesn't match value count at row 1

This occurs because there is a value specified that MySQL has no idea what to do with.

Securing Data

These queries are very often used to insert data from the user into a database. However, allowing the user to input data into the database can be very insecure at times. If the user decides to enter a single quote, they can stop the query, then enter there own query which will be executed by the database server.

If the query looks like this:

Code:

INSERT INTO people (name) VALUES('$num')

Then the user enters this:

nam');DROP DATA BASE test; )#

that is populated into $num and this query is executed:

Code:

INSERT INTO people (name) VALUES('nam');DROP DATA BASE test; )#');

The single quote that the user entered signals the end of the value then a close parenthesis and semi-colon indicates the end of the query. At this point the INSERT query is valid and I can send another query to do whatever I want. When I am done, I simply use # to comment the rest which allows my bad query to run.

The database server can wrap the value with a call to QUOTE around the inputted values which will secure the input from this kind of attack.

It changes ' to \' which escapes the single quote and prevents it from ending the query. I still believe that this is something the application should do. Why should we even send bad data to the database in the first place?