What is SQL Injection and How to avoid it?

SQL injection happens if the user input is not properly validated

This is just to play around with the database queries


To Avoid SQL Injection

• Encrypt sensitive data.
• Access the database using an account with the least privileges necessary.
• Install the database using an account with the least privileges necessary.
• Ensure that data is valid.
• Do a code review to check for the possibility of second-order attacks.
• Use parameterised queries.
• Use stored procedures.
• Re-validate data in stored procedures.
• Ensure that error messages give nothing away about the internal architecture of the application or the database.

http://www.veracode.com/security/sql-injection

http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev

Maximum length validation in MySQL

The following table describes the maximum length for each type of identifier.

Identifier	Maximum Length (characters)
Database	64
Table	64
Column	64
Index	64
Constraint	64
Stored Procedure or Function	64
Trigger	64
View	64
Alias	256 (see exception following table)
Compound Statement Label	16

Difference between ENUM and SET

ENUM - Accepts only one of the value from the options.
SET  - Accepts one to many values from the options.

How to Check MySQL Version from Command Prompt

Today I was asked to provide MYSQL version which we have used within our integrated system.

We as a developers are not bothered about the version, I was not aware of it and on my local system I have WAMP installed, so it directly gives the MYSQL version.

So to get MYSQL version from “mysql” command line, I got help from one of the site which asked to use the following line of code

select version ();

This will display the current MYSQL version used within system

What is the difference between DDL and DML in MySQL?

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE – to create table in the database
ALTER – alters the table of the database
DROP – delete table from the database
TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT – retrieve data from the a database
INSERT – insert data into a table
UPDATE – updates existing data within a table
DELETE – deletes all records from a table, the space for the records remain