2. SQL Database (3) SQL Views, Injection, Data Types

SQL Views

View: a virtual table based on the result-set of an SQL statement

• can contain rows and columns
• you can add SQL statements & functions to a view
• you can present the data as if the data were coming from one single table

CREATE VIEW

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• example

  CREATE VIEW [Brazil Customers] AS
  SELECT CustomerName, ContactName
  FROM Customers
  WHERE Country = 'Brazil';

Updating a view

• CREATE OR REPLACE VIEW statement

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• example

  CREATE OR REPLACE VIEW [Brazil Customers] AS
  SELECT CustomerName, ContactName, City
  FROM Customers
  WHERE Country = 'Brazil';

DROP VIEW

DROP VIEW *view_name*;

---

SQL Injection

SQL Injection

• a code injection technique that might destroy your database
• one of the most common web hacking techniques
• the placement of malicious code in SQL statements via web page input

---

SQL Data Types

String Data Types

• CHAR(size): A FIXED length string
  • can contain letters, numbers, and special characters
  • size parameter: specifies the column length in characters
    • can be from 0 to 255
    • default is 1
• VARCHAR(size): a VARIABLE length string
  • can contain letters, numbers, and special characters
  • size parameter: specifies the maximum string length in characters
    • can be from 0 to 65535
• BINARY(size): Equal to CHAR(), but stores binary byte strings
  • size parameter: specifies the column length in bytes
    • default is 1
• VARBINARY(size): equal to VARCHAR() but stores binary byte strings
  • size parameter: specifies the maximum column length in bytes
• TINYBLOB: For BLOBs (Binary Large Objects)
  • Max length: 255 bytes
• TINYTEXT: Holds a string with a maximum length of 255 characters
• TEXT(size): Holds a string with a maximum length of 65,535 bytes
• BLOB(size): For BLOBs (Binary Large Objects)
  • Holds up to 65,535 bytes of data
• MEDIUMTEXT: Holds a string with a maximum length of 16,777,215 characters
• MEDIUMBLOB: For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data
• LONGTEXT: Holds a string with a maximum length of 4,294,967,295 characters
• LONGBLOB: For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data
• ENUM(val1, val2, val3, ...): A string object that can have only one value, chosen from a list of possible values
  • can list up to 65535 values in an ENUM list
  • If a value is inserted that is not in the list, a blank value will be inserted
  • values are sorted in the order you enter them
• SET(val1, val2, val3, ...): A string object that can have 0 or more values, chosen from a list of possible values.
  • can list up to 64 values in a SET list

Numeric Data Types

• BIT(size): A bit-value type.
  • size: specifies the number of bits per value
    • can hold a value from 1 to 64.
    • default value for size is 1.
• TINYINT(size): A very small integer.
  • Signed range is from -128 to 127.
  • Unsigned range is from 0 to 255.
  • size parameter: specifies the maximum display width (which is 255)
• BOOL: Zero is considered as false. Nonzero values are considered as true.
• BOOLEAN: equal to BOOL
• SMALLINT(size): A small integer.
  • Signed range is from -32768 to 32767.
  • Unsigned range is from 0 to 65535.
  • size parameter specifies the maximum display width (which is 255)
• MEDIUMINT(size): A medium integer.
  • Signed range is from -8388608 to 8388607.
  • Unsigned range is from 0 to 16777215
  • size parameter specifies the maximum display width (which is 255)
• INT(size): A medium integer.
  • Signed range is from -2147483648 to 2147483647.
  • Unsigned range is from 0 to 4294967295.
    - size parameter specifies the maximum display width (which is 255)
• INTEGER(size): equal to INT(size)
• BIGINT(size): A large integer.
  • Signed range is from -9223372036854775808 to 9223372036854775807.
  • Unsigned range is from 0 to 18446744073709551615.
  • size parameter specifies the maximum display width (which is 255)
• FLOAT(p): A floating point number.
  • p value: to determine whether to use FLOAT or DOUBLE for the resulting data type.
    • If p is from 0 to 24, the data type becomes FLOAT()
    • If p is from 25 to 53, the data type becomes DOUBLE()
• DECIMAL(size, d): An exact fixed-point number.
  • size: specifies the total number of digits
    • maximum: 65
    • default: 10
  • d: the number of digits after the decimal point
    • maximum number: 30
    • default: 0
• DEC(size, d): equal to DECIMAL(size, d)

Date and Time Data Types

• DATE: A date.
  • format: YYYY-MM-DD
  • supported range: from ‘1000-01-01’ to ‘9999-12-31’
• DATETIME(fsp):A date and time combination.
  • format: YYYY-MM-DD hh:mm:ss
  • supported range: from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
  • add to column defintion
    • DEFAULT: get automatic intialization
    • ON UPDATE: update to the current date and time
• TIMESTAMP(fsp): A timestamp.
  • TIMESTAMP values are stored as the number of seconds since the Unix epoch (’1970-01-01 00:00:00’ UTC)
  • format: YYYY-MM-DD hh:mm:ss
  • supported range: ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC
  • add to column definition
    • DEFAULT CURRENT_TIMESTAMP: automatic initialization
    • ON UPDATE CURRENT_TIMESTAMP: update to the current date and time
• TIME(fsp): A time.
  • format: hh:mm:ss
  • supported range: from ‘-838:59:59’ to ‘838:59:59’
• YEAR: A year in four-digit format
  • values allowed in four digit format: 1901 to 2155, and 0000