SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language.
This tutorial will give you a quick start to SQL. It covers most of the
topics required for a basic understanding of SQL and to get a feel of
how it works.
The SQL CREATE DATABASE statement is used to create a new SQL database.
DROP DATABASE DatabaseName; Always the database name should be unique within the RDBMS.
SQL> DROP DATABASE testDB; NOTE − Be careful before using this operation because by deleting an existing database would result in loss of complete information stored in the database.
Make sure you have the admin privilege before dropping any database. Once a database is dropped, you can check it in the list of the databases as shown below −
SQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | AMROOD | | TUTORIALSPOINT | | mysql | | orig | | test | +--------------------+ 6 rows in set (0.00 sec)
When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.
The SQL USE statement is used to select any existing database in the SQL schema.
If you take a user input through a webpage and insert it into a SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script.
Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching.
In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (modify these rules as needed).
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) { $result = mysql_query("SELECT * FROM CUSTOMERS WHERE name = $matches[0]"); } else { echo "user name not accepted"; } To demonstrate the problem, consider this excerpt −
// supposed input $name = "Qadir'; DELETE FROM CUSTOMERS;"; mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'"); The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table.
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails.
However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem.
if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");
$sub = addcslashes(mysql_real_escape_string("%str"), "%_"); // $sub == \%str\_ mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");
DDL - Data Definition Language
| Sr.No. | Command & Description |
|---|---|
| 1 | CREATE Creates a new table, a view of a table, or other object in the database. |
| 2 | ALTER Modifies an existing database object, such as a table. |
| 3 | DROP Deletes an entire table, a view of a table or other objects in the database. |
DML - Data Manipulation Language
| Sr.No. | Command & Description |
|---|---|
| 1 | SELECT Retrieves certain records from one or more tables. |
| 2 | INSERT Creates a record. |
| 3 | UPDATE Modifies records. |
| 4 | DELETE Deletes records. |
DCL - Data Control Language
| Sr.No. | Command & Description |
|---|---|
| 1 | GRANT Gives a privilege to user. |
| 2 | REVOKE Takes back privileges granted from user. |
The SQL CREATE DATABASE statement is used to create a new SQL database.
Syntax
The basic syntax of this CREATE DATABASE statement is as follows −CREATE DATABASE DatabaseName;Always the database name should be unique within the RDBMS.
Example
If you want to create a new database <testDB>, then the CREATE DATABASE statement would be as shown below −SQL> CREATE DATABASE testDB;Make sure you have the admin privilege before creating any database. Once a database is created, you can check it in the list of databases as follows −
SQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | AMROOD | | TUTORIALSPOINT | | mysql | | orig | | test | | testDB | +--------------------+ 7 rows in set (0.00 sec)
The SQL DROP DATABASE statement is used to drop an existing database in SQL schema.
Syntax
The basic syntax of DROP DATABASE statement is as follows −DROP DATABASE DatabaseName; Always the database name should be unique within the RDBMS.
Example
If you want to delete an existing database <testDB>, then the DROP DATABASE statement would be as shown below −SQL> DROP DATABASE testDB; NOTE − Be careful before using this operation because by deleting an existing database would result in loss of complete information stored in the database.
Make sure you have the admin privilege before dropping any database. Once a database is dropped, you can check it in the list of the databases as shown below −
SQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | AMROOD | | TUTORIALSPOINT | | mysql | | orig | | test | +--------------------+ 6 rows in set (0.00 sec)
When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.
The SQL USE statement is used to select any existing database in the SQL schema.
Syntax
The basic syntax of the USE statement is as shown below −USE DatabaseName;Always the database name should be unique within the RDBMS.
Example
You can check the available databases as shown below −SQL> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | AMROOD | | TUTORIALSPOINT | | mysql | | orig | | test | +--------------------+ 6 rows in set (0.00 sec)Now, if you want to work with the AMROOD database, then you can execute the following SQL command and start working with the AMROOD database.
SQL> USE AMROOD;
SQL - Injection
If you take a user input through a webpage and insert it into a SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script.
Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching.
In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (modify these rules as needed).
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) { $result = mysql_query("SELECT * FROM CUSTOMERS WHERE name = $matches[0]"); } else { echo "user name not accepted"; } To demonstrate the problem, consider this excerpt −
// supposed input $name = "Qadir'; DELETE FROM CUSTOMERS;"; mysql_query("SELECT * FROM CUSTOMSRS WHERE name='{$name}'"); The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table.
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails.
However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem.
Preventing SQL Injection
You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.if (get_magic_quotes_gpc()) { $name = stripslashes($name); } $name = mysql_real_escape_string($name); mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");
The LIKE Quandary
To address the LIKE quandary, a custom escaping mechanism must convert user-supplied '%' and '_' characters to literals. Use addcslashes(), a function that lets you specify a character range to escape.$sub = addcslashes(mysql_real_escape_string("%str"), "%_"); // $sub == \%str\_ mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");
Komentar
Posting Komentar