SQL Constraint Types
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are unique
PRIMARY KEY - Uniquely identifies each row in a table (a combination of
a NOT NULL and UNIQUE)
FOREIGN KEY - Establishes a link between data in two tables, and
prevents action that will destroy the link between them
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Creates indexes on columns to retrieve data from the
database faster
SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values.
NOT NULL on CREATE TABLE
Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
NOT NULL on ALTER TABLE
Syntax for SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;
Syntax for My SQL:
ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;
Remove a NOT NULL Constraint
Syntax for SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN Age int NULL;
Syntax for My SQL:
ALTER TABLE Persons
MODIFY COLUMN Age int NULL;
SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are unique.
UNIQUE Constraint on CREATE TABLE
The following SQL defines a UNIQUE constraint for the "ID" column upon
creation of the "Persons" table:
SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
MySQL:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
Naming a Unique Constraint
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple
columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
UNIQUE Constraint on ALTER TABLE
To create a UNIQUE constraint on the "ID" column when the table is already
created, use the following SQL syntax:
ALTER TABLE Persons
ADD UNIQUE (ID);
Naming a Unique Constraint
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple
columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
Drop a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP INDEX UC_Person;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column upon creation of
the "Persons" table:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
PRIMARY KEY on Multiple Columns
To define an un-named PRIMARY KEY constraint on multiple columns, use the
following SQL syntax:
CREATE TABLE Persons (
ID int,
LastName varchar(255),
FirstName varchar(255),
Age int,
PRIMARY KEY (ID, LastName)
);
To define a named PRIMARY KEY constraint on multiple columns, use the
following SQL syntax:
CREATE TABLE Persons (
ID int,
LastName varchar(255),
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID, LastName)
);
PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table already
has been created, use the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
PRIMARY KEY on Multiple Columns
To define a named PRIMARY KEY constraint on multiple columns, use the
following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);
Drop a PRIMARY KEY Constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint establishes a link between two tables, and prevents
action that will destroy the link between them.
The FOREIGN KEY constraint also prevents you from deleting a record in the
parent table, if related rows still exist in the child table.
Assume we have two tables:
Persons Table
PersonID LastName FirstName Age
1 Hansen Ola 30
2 Svendson Tove 23
Orders Table
OrderID OrderNumber PersonID
3 22456 2
4 24562 1
Here we see that the "PersonID" column in the "Orders" table points to the
"PersonID" column in the "Persons" table.
FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY constraint on the "PersonID" column
upon creation of the "Orders" table:
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int,
CONSTRAINT fk_Person
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column after the
"Orders" table is created, use the following SQL:
ALTER TABLE Orders
ADD CONSTRAINT fk_Person
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);
Drop a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_Person;
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_Person;
SQL CHECK Constraint
The CHECK constraint is used to ensure that the values in a column satisfies a
specific condition.
CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the "Age" column upon
creation of the "Persons" table.
Here, the CHECK constraint ensures that the "Age" column must have a value of
18, or above:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age >= 18)
);
Naming a CHECK Constraint
To name a CHECK constraint, and to define a CHECK constraint on multiple
columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT chk_PersonAge CHECK (Age >= 18 AND City = 'Sandnes')
);
CHECK Constraint on ALTER TABLE
To create a CHECK constraint on the "Age" column when the table is already
created, use the following SQL:
ALTER TABLE Persons
ADD CHECK (Age >= 18);
Naming a CHECK Constraint
To name a CHECK constraint, and to define a CHECK constraint on multiple
columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT chk_PersonAge CHECK (Age >= 18 AND City
= 'Sandnes');
Drop a CHECK Constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK chk_PersonAge;
SQL DEFAULT Constraint
The DEFAULT constraint is used to automatically insert a default value for a
column, if no value is specified.
DEFAULT Constraint on CREATE TABLE
The following SQL sets a DEFAULT value for the "City" column upon creation of
the "Persons" table:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
The DEFAULT constraint can also be used to insert system values, by using
functions like CURRENT_DATE() to insert the current date:
MySQL:
CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CURRENT_DATE()
);
SQL Server:
To achieve the same result in SQL Server use the following SQL (to insert the
current date):
CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CAST(GETDATE() AS date)
);
DEFAULT Constraint on ALTER TABLE
To define a DEFAULT constraint on the "City" column when the table is already
created, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
SQL Server:
ALTER TABLE Persons
ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
Oracle:
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
Drop a DEFAULT Constraint
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server:
ALTER TABLE Persons
DROP CONSTRAINT df_City;
MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
Oracle:
ALTER TABLE Persons
MODIFY (City DEFAULT NULL);
SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes on tables in databases, to
speed up data retrieval.
Types of Indexes: Non-unique and Unique
There are two types of indexes:
CREATE INDEX - Creates a non-unique index (duplicate values are
allowed)
CREATE UNIQUE INDEX - Creates a unique index (duplicate values are
not allowed)
CREATE INDEX Syntax
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX Example
The following SQL creates a non-unique index named "idx_lastname" on the
"LastName" column in the "Persons" table:
CREATE INDEX idx_lastname
ON Persons (LastName);
If you want to create an index on a combination of columns, you can list the
column names within the parentheses, separated by commas:
CREATE INDEX idx_lname_fname
ON Persons (LastName, FirstName);
DROP INDEX Statement
SQL Server:
DROP INDEX table_name.index_name;
MySQL:
ALTER TABLE table_name
DROP INDEX index_name;
MS Access:
DROP INDEX index_name ON table_name;
DB2/Oracle:
DROP INDEX index_name;
SQL AUTO INCREMENT Field
An auto-increment field is a numeric column that automatically generates a unique
number, when a new record is inserted into a table.
The auto-increment field is typically the PRIMARY KEY field that we want to
automatically be assigned a unique number, every time a new record is inserted.
Syntax for MySQL
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment
feature.
The following SQL defines the "Personid" column to be an auto-increment primary
key field in the "Persons" table:
CREATE TABLE Persons (
Personid int AUTO_INCREMENT PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
To let AUTO_INCREMENT start with another value, use the following SQL
statement:
ALTER TABLE Persons AUTO_INCREMENT = 100;
When we insert a new record into the "Persons" table, we will NOT have to specify
a value for the "Personid" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');
Syntax for SQL Server
The SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
The following SQL defines the "Personid" column to be an auto-increment primary
key field in the "Persons" table:
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
When we insert a new record into the "Persons" table, we will NOT have to specify
a value for the "Personid" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');
Syntax for MS Access
The MS Access uses the AUTOINCREMENT keyword to perform an auto-
increment feature.
The following SQL statement defines the "Personid" column to be an auto-
increment primary key field in the "Persons" table:
CREATE TABLE Persons (
Personid AUTOINCREMENT PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
The default starting value for AUTOINCREMENT is 1, and it will increment by 1
for each new record.
When we insert a new record into the "Persons" table, we will NOT have to specify
a value for the "Personid" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');
The SQL above inserts a new record into the "Persons" table, and the "Personid"
column will automatically be assigned the next unique number.
Syntax for Oracle
In Oracle, you have to create an auto-increment field with the SEQUENCE object
(this object generates a number sequence).
Here is the CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
The code above creates a SEQUENCE object called seq_person, that starts with 1
and will increment by 1. It will also cache up to 10 values for performance. The
cache option specifies how many sequence values will be stored in memory for
faster access.
When we insert a new record into the "Persons" table, we will have to use the
nextval function (this function retrieves the next value from seq_person sequence):
INSERT INTO Persons (Personid, FirstName, LastName)
VALUES (seq_person.nextval, 'Lars', 'Monsen');
The SQL above inserts a new record into the "Persons" table, and the "Personid"
column would be assigned the next unique number from the seq_person sequence.
SQL Dates
SQL Date Data Types
Different SQL databases have various data types to store date and time values.
MySQL has the following date data types:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
TIME - format: HH:MI:SS
YEAR - format YYYY or YY
SQL Server has the following date data types:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIME - format: HH:MI:SS
TIMESTAMP - format: a unique number
Note: The date data type are defined for a column upon creation of a new table in
your database.
SQL Working with Dates
Look at the following table:
Orders Table
OrderId ProductName OrderDate
1 Geitost 2025-11-11
2 Camembert Pierrot 2025-11-09
3 Mozzarella di Giovanni 2025-11-11
4 Mascarpone Fabioli 2025-10-29
Now we want to select the records with an OrderDate of "2025-11-11" from the
table above.
We use the following SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2025-11-11'
The result-set will look like this:
OrderId ProductName OrderDate
1 Geitost 2025-11-11
3 Mozzarella di Giovanni 2025-11-11
Note: Two dates can easily be compared if there is no time component involved!
Now, assume that the "Orders" table looks like this (notice the added time-
component in the "OrderDate" column):
OrderId ProductName OrderDate
1 Geitost 2025-11-11 13:23:44
2 Camembert Pierrot 2025-11-09 15:45:21
3 Mozzarella di Giovanni 2025-11-11 11:12:01
4 Mascarpone Fabioli 2025-10-29 14:56:59
If we use the same SELECT statement as above:
SELECT * FROM Orders WHERE OrderDate='2025-11-11'
we will get no result! This is because the query is looking only for dates with no
time portion.
Tip: To keep your queries simple and easy to maintain, do not use time-components
in your dates, unless you have to!
SQL CREATE VIEW Statement
An SQL view is a virtual table based on the result-set of an SQL statement. An SQL
view contains rows and columns, just like a real table. The fields in the view are
fields from one or more real tables in the database.
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
CREATE VIEW Examples
The following SQL creates a view named "Brazil Customers", that shows all
customers from Brazil:
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
To query the view above, use the following SQL syntax:
Example
SELECT * FROM [Brazil Customers];
The following SQL creates a view named "Products Above Average Price", that
selects all products in the "Products" table with a Price higher than the average
price:
Example
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
To query the view above, use the following SQL syntax:
Example
SELECT * FROM [Products Above Average Price];
ALTER VIEW Statement (SQL Server)
In SQL Server, a view can be updated with the ALTER VIEW statement.
ALTER VIEW Syntax
ALTER VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
The following SQL adds the "City" column to the "Brazil Customers" view:
Example
ALTER VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
CREATE OR REPLACE VIEW Statement (MySQL and Oracle)
In MySQL and Oracle, a view can be updated with the CREATE OR REPLACE
VIEW statement.
CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
The following SQL adds the "City" column to the "Brazil Customers" view:
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
DROP VIEW Statement
A view is deleted with the DROP VIEW statement.
DROP VIEW Syntax
DROP VIEW view_name;
The following SQL deletes the "Brazil Customers" view:
Example
DROP VIEW [Brazil Customers];
SQL Injection
SQL injection is a code injection technique that can destroy your database. SQL
injections are a common web hacking technique.
Look at the following example which creates a SELECT statement by adding a
variable (txtUserId) to a select string. The variable is fetched from user input
(getRequestString):
Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
The next chapters show the most effective methods to prevent SQL injections,
using SQL Parameters and SQL Prepared Statements.
SQL Injection Based on 1=1 is Always True
Look at the example above again. The original purpose of the SQL code was to
select a user with a given user id.
If there is nothing to prevent a user from entering "wrong" input, the user can enter
some "smart" input like this:
UserId: 105 OR 1=1
Then, the SQL statement will look like this:
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
The SQL above is valid and will return ALL rows from the "Users" table, since OR
1=1 is always TRUE.
Does the example above look dangerous? What if the "Users" table contains names
and passwords?
A hacker might get access to all the user names and passwords in a database, by
simply inserting 105 OR 1=1 into the input field.
SQL Injection Based on OR ""="" is Always True
Here is an example of a user login on a web site:
Username: John Doe
Password: myPass
Example
uName = getRequestString("username");
uPass = getRequestString("userpassword");
sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' +
uPass + ' " '
Result
SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"
A hacker might get access to user names and passwords in a database by simply
inserting " OR ""=" into the user name or password text box:
User Name: “or”“=”
Password: “or”“=”
The SQL statement will now look like this:
Result
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""
The SQL above is valid and will return ALL rows from the "Users" table, since OR
""="" is always TRUE.
SQL Injection From Batched SQL Statements
Batched SQL statements is a group of two or more SQL statements, separated by
semicolons.
The SQL statement below will return all rows from the "Users" table, then delete
the "Suppliers" table.
Example
SELECT * FROM Users; DROP TABLE Suppliers;
Look at the following example:
Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
And the following input:
User id: 105; DROP TABLE Suppliers
The valid SQL statement would look like this:
Result
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
SQL Parameters - Prevent SQL Injection
SQL parameters (Parameterized Queries) can be used to protect a web site from
SQL injections.
Most databases support parameterized queries, but the syntax varies:
MySQL use ? for parameters
SQL Server uses @ for parameters
PostgreSQL uses $ for parameters
SQL parameters are added to an SQL query at execution time, in a controlled
manner.
[Link] Razor Example
userid = getRequestString("UserId");
query = "SELECT * FROM Users WHERE UserId = @userid";
[Link](query, userid);
Note that parameters in SQL Server are presented by a @ marker.
The SQL engine checks each parameter to ensure that it is correct for its column
and are treated literally, and not as part of the SQL to be executed.
Another Example
cname = getRequestString("CustomerName");
caddress = getRequestString("Address");
ccity = getRequestString("City");
query = "INSERT INTO Customers (CustomerName, Address, City)
Values(@cname, @caddress, @ccity)";
[Link](query, cname, caddress, ccity);
Examples
The following examples shows how to build parameterized queries in some
common web languages.
SELECT STATEMENT IN [Link]:
userid = getRequestString("UserId");
query = "SELECT * FROM Customers WHERE CustomerId = @userid";
cmd = new SqlCommand(query);
[Link]("@userid", userid);
[Link]();
INSERT INTO STATEMENT IN [Link]:
cname = getRequestString("CustomerName");
caddress = getRequestString("Address");
ccity = getRequestString("City");
query = "INSERT INTO Customers (CustomerName, Address, City)
Values(@cname, @caddress, @ccity)";
cmd = new SqlCommand(query);
[Link]("@cname", cname);
[Link]("@caddress", caddress);
[Link]("@ccity", ccity);
[Link]();
SQL Prepared Statements - Prevent SQL Injection
SQL prepared statements can be used to protect a web site from SQL injections.
Prepared statements seperates the query structure (the SQL) from the actual data
(user input).
Prepared statements basically work like this:
1. Prepare: An SQL query template with placeholders is sent to the server. The
data values are not sent. Example: INSERT INTO MyGuests VALUES(?, ?,
?). Then, the server parses, compiles, and optimizes the SQL query template,
without executing it
2. Execute: At a later time, the application binds the values to the parameters,
and the database executes the query. The application may execute the query
as many times as it wants with different values
Prepared statements have four main advantages:
Reduced parsing time - as the preparation on the query is done only once
(although the statement is executed multiple times)
Minimize bandwidth - Bound parameters minimize bandwidth to the server
as you need send only the parameters each time, and not the whole query
Security - Prepared statements are very useful against SQL injections,
because parameter values, which are transmitted later using a different
protocol, need not be correctly escaped. If the original statement template is
not derived from external input, SQL injection cannot occur
Cleaner code - by seperating data from SQL commands
Prepared Statements in MySQL
The following example is taken from PHP MySQL Prepared Statements, and uses
prepared statements in MySQL:
Example - MySQL with Prepared Statement
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query template
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)";
// Prepare the SQL query template
if($stmt = $conn->prepare($sql)) {
// Bind parameters
$stmt->bind_param("sss", $firstname, $lastname, $email);
// Set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@[Link]";
$stmt->execute();
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@[Link]";
$stmt->execute();
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@[Link]";
$stmt->execute();
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$stmt->close();
$conn->close();
?>
Code Explanation
In the SQL, the question marks (?) are placeholders for firstname, lastname,
and email values:
"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"
Now, look at the bind_param() function. This function bind variables to the
placeholders in the SQL query. The placeholders (?) will be replaced by the actual
values held in the variables at the time of execution. The "sss" argument lists
the type of data each parameter is. The s character tells mysql that the parameter
is a string. We must define one of these for EACH parameter. By telling mysql what
type of data to expect, we minimize the risk of SQL injections:
$stmt->bind_param("sss", $firstname, $lastname, $email);
The type argument can be one of four types:
i - integer (whole number)
d - double (floating point number)
s - string (text)
b - binary (image, PDF, etc.)
Note: If we want to insert data from external sources (like user input), it is very
important that the data is sanitized and validated.
SQL Hosting
If you want your web site to store and retrieve data from a database, your web
server must have access to a database-system.
Some common SQL hosting databases are MySQL, PostgreSQL, SQL Server, and
Oracle.
MySQL
MySQL is a popular database software for web sites.
MySQL is known for speed, reliability, and ease of use.
MySQL is an inexpensive alternative to the expensive Microsoft and Oracle
solutions.
SQL Server
Microsoft SQL Server is a popular database software for database-driven web sites
with high traffic.
Microsoft SQL Server has a strong integration with other Microsoft products,
powerful tools, and robust security features.
Oracle
Oracle is a popular database software for database-driven web sites with high
traffic.
Oracle is known for extreme scalability, performance, reliability, and security.
Oracle has expensive licensing.
PostgreSQL
PostgreSQL is a popular database software for web sites.
PostgreSQL has advanced features, high standards compliance, extensibility, and
strong data integrity.
PostgreSQL is open-source and free.