SQL PREPARATION TIME NOTE 2
In this "SQL PREPARATION TIME NOTE 2" article, You can achieve a very high degree of knowledge in SQL in a very effective way and it's less time-consuming. As with the “SQL PREPARATION TIME NOTE 1” article, this article also has a Teacher table, and with the help of this table, let's continue this lesson.
SHORT TIME PREPARATION NOTE
| ID | DESCRIPTION | FIRST_NAME | LAST_NAME | PHONE | |
|---|---|---|---|---|---|
| 1 | DBMS | Alfreds | kuster | alfredskuster@yahoo.com | 572222132 |
| 2 | C++ | Tom | Riddle | Tomriddle@yahoo.com | 454522132 |
| 3 | DBMS | Harry | Jonathan | harryJonathan@yahoo.com | 258656854 |
| 4 | PYTHON | Enrique | harn | enriqueharn@yahoo.com | 452536425 |
| 5 | C++ | Kelvin | Mitnick | kelvinMitnick@yahoo.com | 453656854 |
| 6 | PYTHON | Michael | calce | Michaelcalce@yahoo.com | 487656854 |
| 7 | - | John | kart | Johncart@yahoo.com | 654256854 |
INSERT INTO
You know that everything in the DBMS system done based on data. So, Insertion of data is mandatory. You can insert data into a table using the "INSERT INTO" command. Insertion can be done in 3 ways, or 2 ways depend on the DBMS system.
Syntax:
// Way of insertion 1
Insert into table_name values(expr1, expr2,.. ,expr_n);
// Way of insertion 2
Insert into table_name (column1, column2, ..,columnN) values(expr1, expr2,.. ,expr_n);
// Way of insertion 3
Insert all
INTO table_name (column1, column2,...,column_n) VALUES (expr1, expr2,.. ,expr_n)
INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;
Example:
insert into teacher values(0003, 'DBMS', 'Harry', 'Jonathan', 'harryJonathan@yahoo.com', '258656854')
insert into teacher (Id,Description,First_name,Last_name,Email,Phone) values(0004, 'PYTHON', 'Enrique', 'harn', 'enriqueharn@yahoo.com', '452536425')In the 1st way of insertion, All column values should be given. In the 2nd way of insertion, Specific column values should be given. This two way follows 1 row inserted in one-time execution. In the case of many insertions at once, you should consider the 3rd way of insertion, it enters values only for specified columns. The remaining all represented as NULL. XAMPP, Live SQL does not support the 3rd way of representation.
Insertion of text and numeric fields needs proper quotations, Unique values and maintain all constraints(conditions).
UPDATE
The UPDATE command comes after the INSERT command. Update Command is used in order to update existing records in a table.
Syntax
Update table_name set column1=value1,column2=value2,...,columnN=valueN
WHERE some_column=some_value;In Update command, it is mandatory to use SET clause with WHERE clause. If no specific column is selected then all records in the table is updated.
Similar to Insertion, Updation time you must check once the datatype of the specified columns.
Use Primary key or Unique key values as a condition when update otherwise, data loss when a table has multiple records with the same value.
DELETE
The Delete command is used to delete rows in a table.
Similar to Update and Insert, Please careful about key constraints, data types, and then perform the deletion. You cannot undo this command.
Records can be fetched from DBMS recycle bin. This given link demonstrates all operations related recycle bin. https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#ADMIN11681
Must remember, Prepare for only those portions where you can able to explain.
- Suggestion
Syntax:
DELETE FROM table_name WHERE some_column=some_value;
// Delete All data
DELETE FROM table_name;
DELETE * FROM table_name;
Example:
Delete from Student where Id=1;Some_value can be Date datatype, Number datatype, or can be Null also. In Null value deletion, put some_value as Null;
If Where clause part is not specified then all records in a table is deleted.
Just example, You make a College database and you want to clear all records at once, then use this command.
SQL INJECTION
An SQL Injection can destroy your database. Up to this, We have learned Insertion, Updation, and Deletion in SQL.
When SQL is used to display data on a web page, it is common to let web users input their own search values.
SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.
Injected SQL commands can alter SQL statements and compromise the security of a web application.
Since SQL statements are text only, it is easy, with a little piece of computer code, to dynamically change SQL statements to provide the user with selected data:
Server Code
txtTeacherId = getRequestString("TeacherId");
txtSQL = "SELECT * FROM Users WHERE TeacherId = " + txtTeacherId;The example above, creates a select statement by adding a variable (txtTeacherId) to a select string. The variable is fetched from the user input (Request) to the page.
Lets check the potential dangers of using user input in SQL statements.
ALWAYS TRUE SQL INJECTION
SELECT * FROM teacher WHERE 1=1;
SELECT * FROM teacher WHERE 1=2;In the 1st line, The SQL command is valid. It will return all rows from the table, since Where 1=1 is always true.
Does the example above seem dangerous? What if the teacher's table contains Email_id and Phone_no?
The above statement is much the same as this command
SELECT email, Phone FROM Teacher WHERE id=1 or 1=1;| PHONE | |
|---|---|
| alfredskuster@yahoo.com | 572222132 |
| Tomriddle@yahoo.com | 454522132 |
| harryJonathan@yahoo.com | 258656854 |
| enriqueharn@yahoo.com | 452536425 |
| kelvinMitnick@yahoo.com | 453656854 |
| Michaelcalce@yahoo.com | 487656854 |
| Johncart@yahoo.com | 654256854 |
Here is a common construction, used to verify user login to a web site:
A smart hacker might get access to all the user names and passwords in a database by simply inserting 105 or 1=1 into the input box. If you interested in Ethical hacking then read this article: https://www.shoutcoders.com/python-for-ethical-hacking-intro/
| Email_Id | |
|---|---|
| Password | |
Server Code
TEmailId = getRequestString("Email");
TPass = getRequestString("Pass");
sql = "SELECT * FROM Teacher WHERE Email ='" + TEmailId + "' AND Pass ='" + TPass + "'"A smart hacker might get access to user names and passwords in a database by simply inserting " or ""=" into the user name or password text box.
The code at the server will create a valid SQL statement like this:
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""The above SQL is valid. It will return all rows from the table Users, Since WHERE ""="" is always true.
You Might like: SQL INJECTION BASIC: https://www.youtube.com/watch?v=swuxmzXJjBc AND DEMO :
SQL Injection Based on Batched Commands
Most databases support batched SQL commands, separated by semicolons. Thus, Applying more than one command gives results in more danger. Like,
SELECT * FROM Teacher; DROP TABLE Teacher The SQL above will return all rows in the Teacher table, and then delete the table. If we had the following server code:
Server Code
txtTId = getRequestString("Id");
txtSQL = "SELECT * FROM Teacher WHERE Id = " + txtTId;And the following input:
| Email_Id | |
|---|---|
The code at the server would create a valid SQL statement like this:
Result
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;PREVENT SQL INJECTION ATTACKS
Some web developers use a "blacklist" of words or characters to search for in SQL input, to prevent SQL injection attacks.
This is not a very good idea. Many of these words (like delete or drop) and characters (like semicolons and quotation marks), are used in common language, and should be allowed in many types of input.
(In fact it should be perfectly legal to input an SQL statement in a database field.)
The only proven way to protect a web site from SQL injection attacks, is to use SQL parameters.
SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.
ASP.NET Razor Example
txtTId = getRequestString("Email");
txtSQL = "SELECT * FROM Teacher WHERE Email = @0";
db.Execute(txtSQL,txtTId);Note that parameters are represented in the SQL statement 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.
Execute Example :
txtFNam = getRequestString("First_Name");
txtmail = getRequestString("Email_id");
txtPhone = getRequestString("Phone");
txtSQL = "INSERT INTO Teacher (First_Name,Email_id,Phone) Values(@0,@1,@2)";
db.Execute(txtSQL,txtFNam,txtmail,txtPhone);You have just learned to avoid SQL injection. One of the top website vulnerabilities.
The following examples shows how to build parameterized queries in some common web languages.
SELECT COMMAND IN ASP.NET:
txtTId = getRequestString("Id");
sql = "SELECT * FROM Teacher WHERE TeacherId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtTID);
command.ExecuteReader();INSERT INTO COMMAND IN ASP.NET
txtId = getRequestString("Id");
txtDes = getRequestString("Description");
txtFNam = getRequestString("First_Name");
txtLNam = getRequestString("Last_Name");
txtmail = getRequestString("Email");
txtPh = getRequestString("Phone");
txtSQL = "INSERT INTO Teacher (ID,DESCRIPTION,FIRST_NAME,LAST_NAME,EMAIL,PHONE) Values(@0,@1,@2,@3,@4,@5)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtId);
command.Parameters.AddWithValue("@1",txtDes);
command.Parameters.AddWithValue("@2",txtFNam);
command.Parameters.AddWithValue("@3",txtLNam);
command.Parameters.AddWithValue("@4",txtmail);
command.Parameters.AddWithValue("@5",txtPh);
command.ExecuteNonQuery();INSERT INTO COMMAND IN PHP
$stmt = $dbh->prepare("INSERT INTO Teacher(ID,DESCRIPTION,FIRST_NAME,LAST_NAME,EMAIL,PHONE)
VALUES ( :Id , :Des ,:Fnam, :Lnam, :mail, :Ph)");
$stmt->bindParam(':Id', $txtId);
$stmt->bindParam(':Des', $txtDes);
$stmt->bindParam(':Fnam', $txtFNam);
$stmt->bindParam(':Lnam', $txtLNam);
$stmt->bindParam(':mail', $txtmail);
$stmt->bindParam(':Ph', $txtPh);
$stmt->execute();In this way, we can prevent SQL Injection. Commands are broken into several processing commands. Thus, Any kind of danger command can easily be prevented.
You might like: https://www.shoutcoders.com/python-for-ethical-hacking-intro/
Comments
Post a Comment