Authenticate user through LDAP, Active Directory

Please go through the code below - Codeigniter
--------------------------------------------------------------------------------------

Config - define
---------------------------------------------------
define('ldap_serverhost', '192.168.24.31');
define('ldap_basedn', 'domainname');


Controller code - Login
----------------------------------------------------
$username = $this->input->post('userid', TRUE);
$password = $this->input->post('password', TRUE);
$entries = ldap_connention($username, $password);


Helper code - custom_helper
----------------------------------------------------

if (!function_exists('ldap_connention')) {
    /**
     * Create LDAP CONNECTION
     *
     */
    function ldap_connention($handle, $password)
    {

        $server = ldap_serverhost;
        $basedn = ldap_basedn;
        $dn = ldap_basedn . "\\" . $handle;

        $connect = ldap_connect($server);
        ldap_set_option($connect, LDAP_OPT_PROTOCOL_VERSION, 3);
        ldap_set_option($connect, LDAP_OPT_REFERRALS, 0);

//var_dump(ldap_bind($connect, $dn, $password));
        if ($ds = @ldap_bind($connect, $dn, $password)) {
            $filters = "(samaccountname=$handle)";
            //    $filters = "(cn=*)"; // get all results
            $results = ldap_search($connect, "dc=" . ldap_basedn . ",dc=COM", $filters) or exit("unable tosearch");
            $entries = ldap_get_entries($connect, $results);
            if (count($entries) > 0) {
                return $entries;
            } else {
                return 0;
            }

        } else {
            return 0;
        }


    }

Setup IIS server and PHP on window R1,R2 server

Web Platform Installer 5.0 - software  need to install first
---------------------------------

- MySQL windows 5.0
- MS Driver 4.1(x64) for PHP v7.1 for SQLServer in IIS Express
- MS Driver 4.1(x86) for PHP v7.1 for SQLServer in IIS Express
- MS Driver 4.1(x64) for PHP v7.1 for SQLServer in IIS
- MS Driver 4.1(x86) for PHP v7.1 for SQLServer in IIS

- Windows cache extension 2.0(x64) for PHP 7.1 in IIS Express
- Windows cache extension 2.0(x64) for PHP 7.1
- Windows cache extension 2.0(x86) for PHP 7.1 in IIS Express
- Windows cache extension 2.0(x86) for PHP 7.1

- PHP 7.1.1 (x64)
- PHP 7.1.1 (x64) for IIS Express
- PHP 7.1.1 (x86)
- PHP 7.1.1 (x86) for IIS Express

- IIS-web server
- WAS-config-API's
- IIS-HttpCompressionStatic
- IIS-Static
- IIS-DefaultDocument
- IIS-HttpErrors
- IIS-StaticContent
- Windows Azure Pack: Update for service Bus 1.1-.Net Framework 4.6 compatibility..

- MySQL Windows 5.1
- URL Rewrite 2.0
- Microsoft .NET Framework 4.5.2
- Windows Powershell 3.0
- Windows Powershell 3.0
- PHP 5.3.28
- .NET 4.5 for windows 8
- .NET 4.5 Extended with ASP.NET for windows 8
- IIS:CGI

- IIS: WAS Process Model
- IIS: WAS Configuration API
- IIS: Static Content Compression
- IIS: Request Filtering
- IIS: Management Console

- IIS: Request Monitor
- IIS: Logging Tools
- IIS: HTTP Logging

- IIS:  HTTP Errors
- IIS: Directory Browsing
- IIS: DefaultDocument
- IIS: Static Content

Print Star value (****)

Print Star Value

$count = 1;
$limit = 5;
while($count <= $limit){
  for($i=1; $i<=$count; $i++){
      echo '*';
   }
  echo '<br>';
  $count++;
}


OUTPUT
*
**
***
****
*****


----------------------------------------------------------------------------------------------

$count = 5;
while($count !=0){
  for($i=$count; $i>0; $i--){
      echo '*';
  }
  echo '<br>';
  $count--;
}

OUTPUT
*****
****
***
**
*

------------------------------------------------------------------------------------------------
$count = 1;
$limit =5;
while($count <= $limit){
  for($i=1; $i<$count;$i++){
      echo '*';
  }
  echo '<br>';
  $count++;
}
while($count !=0){
  for($j=$count; $j>1; $j--){
    echo '*';
  }
  echo '<br>';
  $count--;
}

OUTPUT
*
**
***
****
*****
****
***
**
*

Session AND Cookie - php ini

;;;;;;;;;;;;;;;;
; File Uploads ;
;;;;;;;;;;;;;;;;

; Whether to allow HTTP file uploads.
; http://php.net/file-uploads
file_uploads = On

; Temporary directory for HTTP uploaded files (will use system default if not
; specified).
; http://php.net/upload-tmp-dir
upload_tmp_dir = "c:/wamp/tmp"

; Maximum allowed size for uploaded files.
; http://php.net/upload-max-filesize
upload_max_filesize = 64M

; Maximum number of files that can be uploaded via a single request

max_file_uploads = 20
---------------------------------------------------------------------------------------------------

Cookies are used to identify sessions. Visit any site that is using cookies and pull up either Chrome inspect element and then network or FireBug if using Firefox.

You can see that there is a header sent to a server and also received called Cookie. Usually it contains some personal information (like an ID) that can be used on the server to identify a session. These cookies stay on your computer and your browser takes care of sending them to only the domains that are identified with it.

If there were no cookies then you would be sending a unique ID on every request via GET or POST. Cookies are like static id's that stay on your computer for some time.

A session is a group of information on the server that is associated with the cookie information. If you're using PHP you can check the session.save_path location and actually "see sessions". They are either files on the server filesystem or backed in a database.

Cookies

• Cookies are stored in browser as text file format.
• It is stored limit amount of data. It is only allowing 4kb[4096bytes]
• It is not holding the multiple variable in cookies.
• We can accessing the cookies values in easily. So it is less secure.
• The setcookie() function must appear BEFORE the tag.

Destroy Cookies:

• If we Closing the browsers at the time.
• Setting the cookie time to expire the cookie.

Example:

<?php

setcookie(name, value, expire, path, domain, secure, httponly);
$cookie_uame = "codingslover";
$cookie_uvalue = "website";

//set cookies for 1 hour time
setcookie($cookie_uname, $cookie_uvalue, 3600, "/");

//expire cookies
setcookie($cookie_uname,"",-3600);

?>

Sessions

• Sessions are stored in server side.
• It is stored unlimited amount of data.
• It is holding the multiple variable in sessions.

Destroy Sessions :

• Using unset() session, we will destroyed the sessions.
• Using session_destory(), we we will destroyed the sessions.

Example:

<?php

session_start();

//session variable
$_SESSION['testvaraible'] = 'Codings';

//destroyed the entire sessions
session_destroy(); 

//Destroyed the session variable "testvaraible".
unset($_SESSION['testvaraible']);

?>

MySql

Second Max Salary - Mysql
----------------------------------
SELECT *,max(salary) as maxId FROM employee
WHERE salary < (select max(salary) FROM employee);


Duplicate email id more then 2
----------------------------------
SELECT *, count(*) as emp_email_count FROM employee GROUP BY emp_email HAVING emp_email_count = 1 ;


MySql file storing path
-----------------------------------
Usually in the /mysql/data directory of your WAMP installation. You'll recognize the location because every database has a folder with the same name there. You can change the location of the data directory using the datadir setting in my.cnf 

Where the SQL Database files are stored?

For example C:\Users\--user--\AppData\Local\Microsoft\Microsoft SQL Server LocalDB\Instances\LocalDBApp1. User database files are stored where the user designates, typically somewhere in the C:\Users\\Documents\ folder. By default, LocalDB database creates “*.mdf” files in the C:/Users/"username" directory.

------------------------------------------------------------------------

MYISAM:

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

Difference between MyISAM and InnoDB : -

The most commonly used storage engine in MySQL are MyISAM and InnoDB.

With these storage engine there are some advantages and disadvantages according to application needs.

As you all know, the default storage engine chosen by MySQL database is MyISAM.

The main difference between MyISAM and INNODB are :

• MyISAM does not support transactions by tables while InnoDB supports.
• There are no possibility of row-level locking, relational integrity in MyISAM but with InnoDB this is possible. MyISAM has table-level locking.
• InnoDB does not support FULLTEXT index while MyISAM supports.
• Performance speed of MyISAM table is much higher as compared with tables in InnoDB.
• InnoDB is better option while you are dealing with larger database because it supports transactions, volume while MyISAM is suitable for small project.
• As InnoDB supports row-level locking which means inserting and updating is much faster as compared with MyISAM.
• InnoDB supports ACID (Atomicity, Consistency, Isolation and Durability) property while MyISAM does not support.
• In InnoDB table,AUTO_INCREMENT field is a part of index.
• Once table in InnoDB is deleted then it can not re-establish.
• InnoDB does not save data as table level so while implementation of select count(*) from table will again scan the whole table to calculate the number of rows while MyISAM save data as table level so you can easily read out the saved row number.
• MyISAM does not support FOREIGN-KEY referential-integrity constraints while InnoDB supports.

INNER JOIN gets all records that are common between both tables based on the foreign key

LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL

RIGHT JOIN is like the above but gets all records in the RIGHT table

FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table

There are different types of joins available in SQL:

INNER JOIN: returns rows when there is a match in both tables.

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN: It combines the results of both left and right outer joins.

The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

WE can take each first four joins in Details :

We have two tables with the following values.

TableA

id  firstName                  lastName
.......................................
1   arun                        prasanth                 
2   ann                         antony                   
3   sruthy                      abc                      
6   new                         abc                                           

TableB

id2 age Place
................
1   24  kerala
2   24  usa
3   25  ekm
5   24  chennai

....................................................................

INNER JOIN

Note :it gives the intersection of the two tables, i.e. rows they have common in TableA and TableB

Syntax

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;

Result Will Be

firstName       lastName       age  Place
..............................................
arun            prasanth        24  kerala
ann             antony          24  usa
sruthy          abc             25  ekm

LEFT JOIN

Note : will give all selected rows in TableA, plus any common selected rows in TableB.

Syntax

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age   Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL

RIGHT JOIN

Note : will give all selected rows in TableB, plus any common selected rows in TableA.

Syntax

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age     Place
...............................................................................
arun                        prasanth                    24     kerala
ann                         antony                      24     usa
sruthy                      abc                         25     ekm
NULL                        NULL                        24     chennai

FULL JOIN

Note : It is same as union operation, it will return all selected values from both tables.

Syntax

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;

Result

firstName                   lastName                    age    Place
...............................................................................
arun                        prasanth                    24    kerala
ann                         antony                      24    usa
sruthy                      abc                         25    ekm
new                         abc                         NULL  NULL
NULL                        NULL                        24    chennai

Interesting Fact

For INNER joins the order doesn't matter

For (LEFT, RIGHT or FULL) OUTER joins,the order matter



--------------------------------------------------------------------------------------------------------------------------

Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default (for v5.7) and most general-purpose storage engine. MySQL storage engines include both those that handle transaction-safe tables and those that handle nontransaction-safetables.

MySQL Supported Storage Engines

1. InnoDB: InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.
2. MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
3. Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.
4. CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
5. Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
6. Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.
7. NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
8. Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.
9. Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
10. Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.

You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly InnoDB tables, with one CSV table for exporting data to a spreadsheet and a few MEMORY tables for temporary workspaces.

Reference: 1 2

Summary: in this tutorial, you will learn various MySQL table types or storage engines. It is essential to understand the features of each table type in MySQL so that you can use them effectively to maximize the performance of your databases.

MySQL provides various storage engines for its tables as below:

• MyISAM
• InnoDB
• MERGE
• MEMORY (HEAP)
• ARCHIVE
• CSV
• FEDERATED

Each storage engine has its own advantages and disadvantages. It is crucial to understand each storage engine features and choose the most appropriate one for your tables to maximize the performance of the database. In the following sections, we will discuss each storage engine and its features so that you can decide which one to use.

MyISAM

MyISAM extends the former ISAM storage engine. The MyISAM tables are optimized for compression and speed. MyISAM tables are also portable between platforms and operating systems.

The size of MyISAM table can be up to 256TB, which is huge. In addition, MyISAM tables can be compressed into read-only tables to save spaces. At startup, MySQL checks MyISAM tables for corruption and even repairs them in a case of errors. The MyISAM tables are not transaction-safe.

Before MySQL version 5.5, MyISAM is the default storage engine when you create a table without specifying the storage engine explicitly. From version 5.5, MySQL uses InnoDB as the default storage engine.

InnoDB

The InnoDB tables fully support ACID-compliant and transactions. They are also optimal for performance. InnoDB table supports foreign keys, commit, rollback, roll-forward operations. The size of an InnoDB table can be up to 64TB.

Like MyISAM, the InnoDB tables are portable between different platforms and operating systems. MySQL also checks and repairs InnoDB tables, if necessary, at startup.

MERGE

A MERGE table is a virtual table that combines multiple MyISAM tables that have a similar structure into one table. The MERGE storage engine is also known as the MRG_MyISAM engine. The MERGE table does not have its own indexes; it uses indexes of the component tables instead.

Using MERGE table, you can speed up performance when joining multiple tables. MySQL only allows you to perform SELECT, DELETE, UPDATE and INSERT operations on the MERGE tables. If you use DROP TABLE statement on a MERGE table, only MERGE specification is removed. The underlying tables will not be affected.

Memory

The memory tables are stored in memory and use hash indexes so that they are faster than MyISAM tables. The lifetime of the data of the memory tables depends on the uptime of the database server. The memory storage engine is formerly known as HEAP.

Archive

The archive storage engine allows you to store a large number of records, which for archiving purpose, into a compressed format to save disk space. The archive storage engine compresses a record when it is inserted and decompress it using the zlib library as it is read.

The archive tables only allow INSERT and SELECT statements. The ARCHIVE tables do not support indexes, so it is required a full table scanning for reading rows.

CSV

The CSV storage engine stores data in comma-separated values (CSV) file format. A CSV table brings a convenient way to migrate data into non-SQL applications such as spreadsheet software.

CSV table does not support NULL data type. In addition, the read operation requires a full table scan.

FEDERATED

The FEDERATED storage engine allows you to manage data from a remote MySQL server without using cluster or replication technology. The local federated table stores no data. When you query data from a local federated table, the data is pulled automatically from the remote federated tables.

Ref 2.

MySQL 5.7 Supported Storage Engines

• InnoDB: The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, seeChapter 14, The InnoDB Storage Engine.
• MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
• Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.
• CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.
• Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.
• Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.
• NDB (also known as NDBCLUSTER): This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
• Merge: Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.
• Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.
• Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines. It is primarily of interest to developers. The storage engine is a “stub” that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them.

You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine for any table. For example, an application might use mostly InnoDB tables, with one CSV table for exporting data to a spreadsheet and a few MEMORY tables for temporary workspaces.

Choosing a Storage Engine

The various storage engines provided with MySQL are designed with different use cases in mind. The following table provides an overview of some storage engines provided with MySQL:

Table 15.1 Storage Engines Feature Summary

Feature	MyISAM	Memory	InnoDB	Archive	NDB
Storage limits	256TB	RAM	64TB	None	384EB
Transactions	No	No	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes[a]	No	No
B-tree indexes	Yes	Yes	Yes	No	No
T-tree indexes	No	No	No	No	Yes
Hash indexes	No	Yes	No[b]	No	Yes
Full-text search indexes	Yes	No	Yes[c]	No	No
Clustered indexes	No	No	Yes	No	No
Data caches	No	N/A	Yes	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Compressed data	Yes[d]	No	Yes[e]	Yes	No
Encrypted data[f]	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Replication support[g]	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	Yes	No	Yes[h]
Backup / point-in-time recovery[i]	Yes	Yes	Yes	Yes	Yes
Query cache support	Yes	Yes	Yes	Yes	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

^[a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and later.

^[b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

^[c] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and later.

^[d] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

^[e] Compressed InnoDB tables require the InnoDB Barracuda file format.

^[f] Implemented in the server (via encryption functions). Data-at-rest tablespace encryption is available in MySQL 5.7 and later.

^[g] Implemented in the server, rather than in the storage engine.

^[h] Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

^[i] Implemented in the server, rather than in the storage engine.