Unit - 2.DBMS-2-218
Unit - 2.DBMS-2-218
Unit - 2.DBMS-2-218
Output:
7. Cartesian product (X)
• Lets say we have two relations R1 and R2 then the
Cartesian product of these two relations (R1 X R2)
would combine each tuple of first relation R1 with
the each tuple of second relation R2.
• Syntax : R1 X R2
• Query: R XS
• Output:
8. Natural join (⋈)
• Natural join between two or more relations will
result in all the combination of tuples where they
have equal values for the common attribute.
• Example:
create table persons (
id int not null,
lastname varchar(255) not null,
firstname varchar(255),
age int check (age>=18)
);
DEFAULT Constraint
• The DEFAULT constraint is used to provide a
default value for a column.
• Example:
create table orders (
id int not null,
ordernumber int not null,
orderdate date default getdate()
);
AUTO INCREMENT
• Auto-increment allows a unique number to be generated
automatically when a new record is inserted into a table.
• Example:
create table persons (
personid int ,
lastname varchar(255) not null,
firstname varchar(255),
age int
);
Create sequence seq_name min value 1 start with 1
increment by 1 cache 10;
Then we can insert the values into tables
>> insert into persons(personid,lastname, firstname,age)
values (seq_name.nextval, ‘cccc’,’ssss’,22);
3. DML Commands
Prepared by
Dr.S.Sankar
AP/CSE
Sona College of Technology
DML
• A data manipulation language (DML) is a
computer programming language used for
adding (inserting), deleting, and modifying
(updating) data in a database.
Example student table
INSERT command
• Insert command is used to insert data into a
table.
• Syntax:
Insert into table_name values(col1 value,col2
value, ...);
• Example:
Insert into student values(1,’aakash’, 100);
Insert value into only specific columns
• Syntax:
Insert into student (col1_name, col3_name)
values (col1_value, col3_value);
• Example:
insert into student (name) values(‘abishek’);
Insert NULL value to a column
• Syntax:
Insert into student values( col1_value,null);
• Example:
Insert into student values(3,’Akshara’, null);
UPDATE command
• UPDATE command is used to update any
record of data in a table.
• WHERE is used to add a condition to any SQL
query
• Syntax:
update table_name SET column_name =
new_value WHERE some_condition;
• Example:
Update student set mark=100 where regno=2;
Updating Multiple Columns
• UPDATE statement is used to update the
values of multiple columns.
• Table: Student
• Example:
Update student set name=‘aiswarya’,mark=100
where regno=3;
DELETE command
• Syntax:
delete from table_name where column_name=col_value;
• Example:
delete from student where regno=3;
Delete all Records from a Table
• Delete command is used to remove all the
records from the table.
• Syntax:
delete from table_name;
• Example:
delete from student;
Example table: Student
Select Query
• SELECT query is used to retrieve data from a
table.
Select all records from a table
• Syntax:
Select * from table_name;
• Example:
Select * from student;
Select a particular record based on a
condition
• We can use the where clause to set a
condition
• Syntax:
Select * from table_name where col_name=col_val;
• Example:
Select * from student where regno=1;
Operators for WHERE clause condition
SQL LIKE clause
• LIKE clause compares data with an expression
using wildcard operators to match pattern
given in the condition.
Wildcard operators
• There are two wildcard operators that are
used in LIKE clause.
• Percent sign %: represents zero, one or more
than one character.
• Underscore sign _: represents only a single
character.
Sample table
Display the name first letter starts
with ‘a’
• Syntax:
Select * from table_name where col_name like
‘a%’;
• Example:
Select * from student where name like ‘a%’;
Like clause Using _ and %
• Eample:
select * from student where name like ‘_a%’;
• The above query will return all records
from Student table where s_name contain 'd'
as second character.
Using % only
In a table, attribute value starts with any
character.
Example:
select * FROM Student where name like '%i';
Student table where name contain ‘i' as last
character.
ORDER BY Clause
• Order by clause is used with SELECT statement
for arranging retrieved data in sorted order.
• To sort the data in descending order DESC.
• Syntax:
Select *from table_name order by column_name;
• Example:
select * from emp order by salary;
• Example
SELECT * FROM sale GROUP BY customer HAVING
sum(previous_balance) > 3000
DISTINCT keyword
• Output:
AND operator
• AND operator is used to set multiple
conditions with the WHERE clause,
alongwith, SELECT, UPDATE or DELETE SQL
queries.
• Example:
Select * from emp where salary < 10000 and age > 25;
• Output:
Example:
1. To display all the tuple from the database
table.
2. Order by salary
3. Group by salary
Join Queries in SQL
Prepared by
Dr.S.Sankar
AP/CSE
Sona College of Technology
SQL Join statement
• A SQL Join statement is used to combine rows
from two or more tables based on a common
field between them. Different types of Joins
are:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
1. INNER JOIN
• The INNER JOIN keyword selects all rows from
both the tables as long as the condition
satisfies.
• This keyword will create the result-set by
combining all rows from both the tables
where the condition satisfies i.e value of the
common field will be same.
• We can also write JOIN instead of INNER JOIN.
JOIN is same as INNER JOIN.
•
• Syntax:
SELECT
table1.column1,table1.column2,table2.column1,....
FROM table1 INNER JOIN table2 ON
table1.matching_column = table2.matching_column;
Note:
• Although ASSERTION is in the SQL standard, most DBMS does not
support it. Therefore, CHECK and TRIGGERS are commonly used as
work around approaches.
Fall 2020 – University of Virginia © Praphamontripong 2
How Do Assertions Work?
• CREATE ASSERTION <assertion-name>
• CHECK <predicate>;
CREATE ASSERTION …
DB Server
Web Server
$link = mysql_connect($DB_HOST, $DB_USERNAME, $DB_PASSWORD) or
die ("Couldn't connect: " . mysql_error());
mysql_select_db($DB_DATABASE);
$query = "select count(*) from users where username = '$username' and
password = '$password‘ ";
$result = mysql_query($query);
Unauthorized Access Attempt:
password = ’ or 1=1 --
SQL statement becomes:
select count(*) from users where username = ‘user’
and password = ‘’ or 1=1 --
Checks if password is empty OR 1=1, which is always
true, permitting access.
Database Modification Attack:
password = foo’; delete from table users
where username like ‘%
DB executes two SQL statements:
select count(*) from users where username = ‘user’ and password
= ‘foo’
delete from table users where username like ‘%’
1. Leakage of sensitive
information.
2. Reputation decline.
3. Modification of sensitive
information.
4. Loss of control of db server.
5. Data loss.
6. Denial of service.
1. First order attacks
• The attacker can simply enter a malicious
string and cause the modified code to be
executed immediately
2. Second order attacks
• The attacker injects into a persistent storage
(such as a table row) which is deemed as a
trusted source. An attack is subsequently
executed by another activity.
1. Lateral Injection
3. Lateral Injection
The attacker can manipulate the implicit
function To_Char() by changing the values of the
environment
• Injection through user input
First order
• Injection through cookies injection
• Injection through server variables
• Shell injection.
• Websites require constant access to the
database.
• Firewalls provide little or no defense against
SQL injection attacks.
• Your website is public and firewalls must be
set to allow every site visitor access to your
database, usually over port 80/443.
• Antivirus programs are equally ineffective at
blocking SQL injection attacks.
1. Comprehensive data sanitization
• Web sites must filter all user input
• For example, e-mail addresses should be
filtered to allow only the characters allowed in
an e-mail address.
• Its SQL injection defenses can catch most
attempts to sneak SQL through web channels.
2. Use a web application firewall
• A popular example is the free, open source
module ModSecurity.
• ModSecurity provides a sophisticated and
ever-evolving set of rules to filter potentially
dangerous web requests.
3. Limit database privileges by context
• Create multiple database user accounts with
the minimum levels of privilege for their usage
environment.
• For example, the code behind a login page
should query the database using an account
limited only to the relevent credentials table.
• This way, a breach through this channel
cannot be leveraged to compromise the entire
database.
4. Avoid constructing SQL queries with user
input
• Even data sanitization routines can be flawed.
• Using SQL variable binding with prepared
statements or stored procedures is much safer
than constructing full queries.
• Shell injection.
• Scripting language injection.
• File inclusion.
• XML injection.
• XPath injection.
• LDAP injection.
• SMTP injection.
• BSQL Hacker
• SQLmap
• SQLninja
• Safe3 SQL Injector
• SQLSus
• Mole
• Havij
• SQL injection is technique for exploiting
applications that use relational databases as
their back end.
• Applications compose SQL statements and
send to database.
• SQL injection use the fact that many of these
applications concatenate the fixed part of SQL
statement with user-supplied data that forms
WHERE predicates or additional sub-queries.
• The technique is based on malformed user-
supplied data
• Transform the innocent SQL calls to a malicious
call
• Cause unauthorized access, deletion of data, or
theft of information
• All databases can be a target of SQL injection and
all are vulnerable to this technique.
• The vulnerability is in the application layer
outside of the database, and the moment that
the application has a connection into the
database.
• www.google.com
• www.youtube.com
• www.slideshare.net
• www.beyondsecurity.com
• www.wikipedia.org
• www.breakthesecurity.cysecurity.org
• http://www.esecurityplanet.com/
• http://resources.infosecinstitute.com/best-free-and-open-source-sql-
injection-tools/
Triggers in SQL
Prepared by
Dr.S.Sankar
AP/CSE
Sona College of Technology
Trigger
• Triggers are the SQL statements that are executed
automatically when there is any change in the
database.
• For example, a trigger can be invoked when a row
is inserted into a specified table or when certain
table columns are being updated.
(or)
• A trigger is a database object that is associated
with the table, it will be activated when some
events occur.
Triggers are written to be executed in response
to any of the following events.
• A database manipulation (DML) statement
(DELETE, INSERT, or UPDATE).
• A database definition (DDL) statement
(CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON,
LOGOFF, STARTUP, or SHUTDOWN).
Advantages of Triggers
These are the following advantages of Triggers:
• It cannot consume any memory space.
• It executes automatically according to the
event.
• It can directly link with the database table and
perform any changes.
• It can use a DML statement.
• The trigger can be executed when we run the
following statements:
• INSERT
• UPDATE
• DELETE
• Syntax:
create trigger [trigger_name]
[before | after]
{insert | update | delete}
On [table_name]
[for each row]
[trigger_body]
Explanation of syntax:
• create trigger [trigger_name]: Creates or replaces an
existing trigger with the trigger_name.
• [before | after]: This specifies when the trigger will be
executed.
• {insert | update | delete}: This specifies the DML operation.
• on [table_name]: This specifies the name of the table
associated with the trigger.
• [for each row]: This specifies a row-level trigger, i.e., the
trigger will be executed for each row being affected.
• [trigger_body]: This provides the operation to be
performed as trigger is fired
Example
• Create trigger tri
after insert
on student
for each row
begin
dbms_output.put_line(‘one row inserted’);
end;
/
Include this command before creating
trigger
> Set serveroutput on
Condition based Trigger Execution
• Syntax:
create trigger [trigger_name]
[before | after]
{insert | update | delete}
On [table_name]
[for each row]
when (condition)
Declare
Begin
End
Dynamic SQL
Prepared by
Dr.S.Sankar
AP/CSE
Sona College of Technology
What is Dynamic SQL?
• Syntax:
Static SQL
• Static SQL refers to those SQL statements
which are fixed and can be hard coded into
the application.
• As static sqls are fixed queries, these
statements can be analysed and optimized
and do not require any specific handling for
security purpose.
Dynamic SQL
• Dynamic SQL refers to those SQL statements
which are generated dynamically based on
user's input and run in the application.
• Dynamic Sqls helps to develop general and
flexible applications.
• Dynamic SQL may need more permissions and
security handling and a malicious user can
create dangerous code as well.
Ex:No.3 Query the database using
simple and complex queries
Prepared by
Dr.S.Sankar
AP/CSE
Sona College of Technology
Employee table
1. Get all employee details from the employee
table
1. Get all employee details from the employee
table
Select * from employee;
2. Get First_Name,Last_Name from employee
table
2. Get First_Name,Last_Name from employee
table
select to_char(JOINING_DATE,'dd/mm/yyyy
hh:mi:ss') from EMPLOYEE;