Tag Archives: SQL

Recurring Magento 2

Recurring script can be used to run some script that need to executed whenever we issue php bin/magento setup:upgrade irrespective of version change.

The recurring script will be run after the module setup script every time the command line

php bin/magento setup:upgrade

This script will be defined same as InstallSchema class but difference in name of the class. The example for this class you can see in vendor/magento/module-indexer/Setup/Recurring.php

InstallData Magento 2

The InstallData will be run during the module install and its used to load initial data to DB table.

The module install script will run when you run the following command line

php bin/magento setup:upgrade

File : app/code/PHPCodez/First/Setup/InstallData.php

<?php
 namespace PHPCodez\First\Setup;
 use Magento\Framework\Setup\InstallDataInterface;
 use Magento\Framework\Setup\ModuleContextInterface;
 use Magento\Framework\Setup\ModuleDataSetupInterface;
 class InstallData implements InstallDataInterface {
   public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context) {
   $setup->startSetup();
   $setup->getConnection()->query("INSERT INTO phpcodez_post SET name = 'Post 1'");
   $setup->endSetup();
 }
 }

InstallSchema Magento 2

The InstallSchema will be run during the module install and its used to cretae DB table structure.

The module install script will run when you run the following command line

php bin/magento setup:upgrade

File : app/code/PHPCodez/First/Setup/InstallSchema .php

<?php
namespace PHPCodez\First\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;

class InstallSchema implements InstallSchemaInterface
{
 public function upgrade( SchemaSetupInterface $setup, ModuleContextInterface $context ) {
 $installer = $setup;

$installer->startSetup();

if(version_compare($context->getVersion(), '1.1.0', '<')) {
 if (!$installer->tableExists('phpcodez_post')) {
 $table = $installer->getConnection()->newTable(
 $installer->getTable('phpcodez_post')
 )
 ->addColumn(
 'post_id',
 \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
 null,
 [
 'identity' => true,
 'nullable' => false,
 'primary' => true,
 'unsigned' => true,
 ],
 'Post ID'
 )
 ->addColumn(
 'name',
 \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
 255,
 ['nullable => false'],
 'Post Name'
 )
 ->addColumn(
 'created_at',
 \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
 null,
 ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT],
 'Created At'
 )->addColumn(
 'updated_at',
 \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
 null,
 ['nullable' => false, 'default' => \Magento\Framework\DB\Ddl\Table::TIMESTAMP_INIT_UPDATE],
 'Updated At')
 ->setComment('Post Table');
 $installer->getConnection()->createTable($table);

$installer->getConnection()->addIndex(
 $installer->getTable('phpcodez_post'),
 $setup->getIdxName(
 $installer->getTable('phpcodez_post'),
 ['name'],
 \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
 ),
 ['name'],
 \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
 );
 }
 }

$installer->endSetup();
 }
}

SQL Setup Script Magento 2

Some time its necessary to create DB tables and add data to tables. Mageto provides some classes using which you can create DB table structure or add data to tables. Following are the important classes

InstallSchema – This class can be used to setup DB structure when modules is instaled for the first time.
InstallData – This class can be used to load the initial data when modules is insttale for the first time. Continue reading SQL Setup Script Magento 2

SQL injection

SQL injection is a technique often used to attack databases through a website. This is done by including portions of SQL statements in a web form entry field in an attempt to get the website to pass a newly formed rogue SQL command to the database (e.g. dump the database contents to the attacker). SQL injection is a code injection technique that exploits a security vulnerability in a website’s software. The vulnerability happens when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL commands are thus injected from the web form into the database of an application (like queries) to change the database content or dump the database information like credit card or passwords to the attacker. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.

• SQL code is injected into the sql query

• Allows attacker to do almost anything the database user is permitted

• Example sql statement will return all the data from the ‘users’ table:

$sql = "SELECT * FROM users WHERE
username='$user' AND password='$pass'";
$user and $pass contain the value ' OR 1=1"

• Further attack possibilities: insert data, delete data, read data, denial of service…

Counter Measures

• Use prepared statements when supported by the database

• Use database-specific escaping functions when creating the sql statement ex: mysqli_real_escape_string()

• Addslashes() is not a sufficient approach

mysql_connect() vs mysql_pconnect()

Difference between mysql_connect() and mysql_pconnect() PHP

mysql_pconnect() acts very much like mysql_connect() with two major differences.

When connecting using mysql_pconnect() , the function would first try to find a (persistent) link that’s already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

When connecting using mysql_connect(), the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use

Note :mysql_close() will not close links established by mysql_pconnect().