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

Teacher Table Structure
IDDESCRIPTIONFIRST_NAMELAST_NAMEEMAILPHONE
1DBMSAlfredskusteralfredskuster@yahoo.com572222132
2C++TomRiddleTomriddle@yahoo.com454522132
3DBMSHarryJonathanharryJonathan@yahoo.com258656854
4PYTHONEnriqueharnenriqueharn@yahoo.com452536425
5C++KelvinMitnickkelvinMitnick@yahoo.com453656854
6PYTHONMichaelcalceMichaelcalce@yahoo.com487656854
7JohnkartJohncart@yahoo.com654256854
Teacher Table

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:

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

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:

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.

SQLi
SQLi

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

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

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

EMAILPHONE
alfredskuster@yahoo.com572222132
Tomriddle@yahoo.com454522132
harryJonathan@yahoo.com258656854
enriqueharn@yahoo.com452536425
kelvinMitnick@yahoo.com453656854
Michaelcalce@yahoo.com487656854
Johncart@yahoo.com654256854
Output of above Query

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

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:

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 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,

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

And the following input:

Email_Id

The code at the server would create a valid SQL statement like this:

Result

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

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 :

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:

INSERT INTO COMMAND IN ASP.NET

INSERT INTO COMMAND IN PHP

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/

Leave a Reply

Your email address will not be published. Required fields are marked *

Close Bitnami banner
Bitnami