How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to&nbsp;<span style="color: rgb(0, 89, 153);">SQL injection</span>, like in the following example:

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input']; 
mysql_query("INSERT INTO table (column) VALUES ('$unsafe_variable')");

That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:

INSERT INTO table (column) VALUES('value'); DROP TABLE table;--')

What can be done to prevent this from happening?

3 step: Building PHP apps using SQL Server on Windows

Step 1: Set up your environment

In this section, you will get SQL Server on Windows. After that you will install the necessary dependencies to create PHP apps with SQL Server

1. Install SQL Server

1. If you don’t have SQL Server Developer (or above) installed, click here to download the SQL Server exe.
2. Run it to start the SQL installer.
3. Click Basic in Select an installation type.
4. Click Accept after you have read the license terms.
5. (Optional) if you need to, you can choose a custom installation location for SQL Server.
6. Click Install to proceed with the installation.

You now have SQL Server installed and running locally on your Windows computer! Check out the next section to continue installing prerequisites.

2. Install PHP and Chocolatey

You can download PHP using the Web Platform Installer. Once you download Web PI, open it up and download the entry which says ‘PHP 7.2.7 (x64) for IIS Express’.

Next, install Chocolatey. Chocolatey is a package manager like apt-get and yum for Windows. We will use Chocolatey later in the tutorial. Use an elevated Command-line session (run as administrator):

@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

For Chocolatey to work, you now need to restart the terminal session by closing and opening the terminal.

You have succesfully installed PHP and Chocolatey on your machine!

3. Install the ODBC Driver and SQL Command Line Utility for SQL Server

SQLCMD is a command line tool that enables you to connect to SQL Server and run queries.

1. Install the ODBC Driver.
2. Install the SQL Server Command Line Utilities.

After installing SQLCMD, you can connect to SQL Server using the following command from a CMD session:

sqlcmd -S localhost -U sa -P your_password
1> # You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements.

This how to run a basic inline query. The results will be printed to STDOUT.

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)

Apr 29 2016 23:23:58

Copyright (c) Microsoft Corporation

Developer Edition (64-bit)
1 rows(s) returned
Executed in 1 ns.

You have successfully installed SQL Server Command Line Utilities on your Windows machine!

Step 2: Create PHP application with SQL server

In this section you will create a simple PHP app. The PHP app will perform basic Insert, Update, Delete, and Select.

1. Install the PHP Drivers for SQL Server

Download the Microsoft PHP Drivers for SQL Server from the download center.

Pick the appropriate dll - for example php_pdo_sqlsrv_72_nts.dll for the PDO Driver and php_sqlsrv_72_nts.dll for the SQLSRV driver.

Copy the dll’s to the C:\Program Files\iis express\PHP\v7.2\ext folder.

Register the dll’s in the php.ini file.

cd C:\Program^ Files\iis^ express\PHP\v7.2\ext

echo extension=php_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini

echo extension=php_pdo_sqlsrv_72_nts.dll >> C:\Program^ Files\iis^ express\PHP\v7.2\php.ini

2. Create a database for your application

Create the database using sqlcmd.

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"

3. Create a PHP app that connects to SQL Server and executes queries

mkdir SqlServerSample

cd SqlServerSample

Using your favorite text editor, create a new file called connect.php in the SqlServerSample folder. Paste the code below inside into the new file.

<?php

$serverName = "localhost";

$connectionOptions = array(

"Database" => "SampleDB",

"Uid" => "sa",

"PWD" => "your_password"

);

//Establishes the connection

$conn = sqlsrv_connect($serverName, $connectionOptions);

if($conn)

echo "Connected!"

?>

Run your PHP script from the terminal.

php connect.php

Connected!

Execute the T-SQL scripts below in the terminal with sqlcmd to create a schema, table, and insert a few rows.

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;"

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;"

Using your favorite text editor, create a new file called crud.php in the SqlServerSample folder. Paste the code below inside into the new file. This will insert, update, delete, and read a few rows.

<?php

$serverName = "localhost";

$connectionOptions = array(

"Database" => "SampleDB",

"Uid" => "sa",

"PWD" => "your_password"

);

//Establishes the connection

$conn = sqlsrv_connect($serverName, $connectionOptions);

//Insert Query

echo ("Inserting a new row into table" . PHP_EOL);

$tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";

$params = array('Jake','United States');

$getResults= sqlsrv_query($conn, $tsql, $params);

$rowsAffected = sqlsrv_rows_affected($getResults);

if ($getResults == FALSE or $rowsAffected == FALSE)

die(FormatErrors(sqlsrv_errors()));

echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);
//Update Query
$userToUpdate = 'Nikita';

$tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";

$params = array('Sweden', $userToUpdate);

echo("Updating Location for user " . $userToUpdate . PHP_EOL);
$getResults= sqlsrv_query($conn, $tsql, $params);

$rowsAffected = sqlsrv_rows_affected($getResults);

if ($getResults == FALSE or $rowsAffected == FALSE)

die(FormatErrors(sqlsrv_errors()));

echo ($rowsAffected. " row(s) updated: " . PHP_EOL);

sqlsrv_free_stmt($getResults);
//Delete Query

$userToDelete = 'Jared';

$tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";

$params = array($userToDelete);

$getResults= sqlsrv_query($conn, $tsql, $params);

echo("Deleting user " . $userToDelete . PHP_EOL);

$rowsAffected = sqlsrv_rows_affected($getResults);

if ($getResults == FALSE or $rowsAffected == FALSE)

die(FormatErrors(sqlsrv_errors()));

echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);

sqlsrv_free_stmt($getResults);
//Read Query

$tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";

$getResults= sqlsrv_query($conn, $tsql);

echo ("Reading data from table" . PHP_EOL);

if ($getResults == FALSE)

die(FormatErrors(sqlsrv_errors()));

while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {

echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);

}

sqlsrv_free_stmt($getResults);
function FormatErrors( $errors )

{

/* Display errors. */

echo "Error information: ";
foreach ( $errors as $error )
{
    echo "SQLSTATE: ".$error['SQLSTATE']."";
    echo "Code: ".$error['code']."";
    echo "Message: ".$error['message']."";
}

}

?>

Run your PHP script from the terminal.

php crud.php

Inserting a new row into table

1 row(s) inserted:

Updating Location for user Nikita

1 row(s) updated:

Deleting user Jared

1 row(s) deleted:

Reading data from table

2 Nikita Sweden

3 Tom Germany

4 Jake United States

Congratulations! You have created your first PHP app with SQL Server! Check out the next section to learn about how you can make your PHP faster with SQL Server’s Columnstore feature.

Step 3: 3 Make your PHP app up to 100x faster

In this section we will show you a simple example of Columnstore Indexes and how they can improve data processing speeds. Columnstore Indexes can achieve up to 100x better performance on analytical workloads and up to 10x better data compression than traditional rowstore indexes.

1. Create a new table with 5 million rows using sqlcmd

sqlcmd -S localhost -U sa -P your_password -d SampleDB -t 60000 -Q "WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))

SELECT TOP(5000000)

ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId

,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId

,a.a * 10 AS Price

,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName

INTO Table_with_5M_rows

FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

2. Create a PHP app that queries this table and measures the time taken

cd ~/

mkdir SqlServerColumnstoreSample

cd SqlServerColumnstoreSample

Using your favorite text editor, create a new file called columnstore.php in the SqlServerColumnstoreSample folder. Paste the following code inside it.

<?php

$time_start = microtime(true);

$serverName = "localhost";

$connectionOptions = array(

"Database" => "SampleDB",

"Uid" => "sa",

"PWD" => "your_password"

);

//Establishes the connection

$conn = sqlsrv_connect($serverName, $connectionOptions);
//Read Query

$tsql= "SELECT SUM(Price) as sum FROM Table_with_5M_rows";

$getResults= sqlsrv_query($conn, $tsql);

echo ("Sum: ");

if ($getResults == FALSE)

die(FormatErrors(sqlsrv_errors()));

while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {

echo ($row['sum'] . PHP_EOL);
}

sqlsrv_free_stmt($getResults);
function FormatErrors( $errors )

{

/* Display errors. */

echo "Error information: ";
foreach ( $errors as $error )
{
    echo "SQLSTATE: ".$error['SQLSTATE']."";
    echo "Code: ".$error['code']."";
    echo "Message: ".$error['message']."";
}

}

$time_end = microtime(true);

$execution_time = round((($time_end - $time_start)*1000),2);

echo 'QueryTime: '.$execution_time.' ms';
?>

3. Measure how long it takes to run the query

Run your PHP script from the terminal.

php columnstore.php

Sum: 50000000

QueryTime: 363ms

4. Add a columnstore index to your table

sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

5. Measure how long it takes to run the query with a columnstore index

php columnstore.php

Sum: 50000000

QueryTime: 5ms

Congratulations! You just made your PHP app faster using Columnstore Indexes!

Check out the PHP Driver on GitHub

Thanks For Visiting, Keep Visiting.

Originally published on sqlchoice

Prepare for PHP Interviews. Set Variable With php.ini File. Logic & Output Behind PHP Code Segment. Class Concept, Error & Functions in PHP. Start PHP Now!

Description
In this course you will be introduced with some tricky questions that everyone face during their interview. In each solutions I have included some useful functions which we generally use at the time of development also. I have covered the following area in my course with Questions, Attractive Presentations and Practical Solutions that will help you to understand the logic behind PHP in a different way.

Some useful functions
PHP error types
Class concept
Access specifiers public, private and protected
Set variable with php.ini file
Operators introduced in PHP7
Logic and its output behind some code segment
Basic knowledge
PHP7, XAMPP Server, Notepad++
What will you learn
This course is build for the person who is facing interviews. Every question is well explained with the practical solution through videos. So that everybody can prepare themselves for the tricky questions asked during interviews
To continue:

PHP - Hypertext pre-processor, a scripting language used by many people in developing web pages, but most of us are unaware even of the full form. To train someone and make them learn this whole language is as difficult and time-consuming as it is...

PHP - Hypertext pre-processor, a scripting language used by many people in developing web pages, but most of us are unaware even of the full form. To train someone and make them learn this whole language is as difficult and time-consuming as it is to learn the language yourself. That’s why PHP developers are there to make your life easy. This article will give us the advantages and requirements of Hire PHP Developer for our very own website project.

First of all, let us understand the value the right developer brings to the project and why it is important for your business.

A website is a major component of any company/business and is very important for its face value, the way it represents the company on the internet is critical for any business to succeed. This is the reason why companies are looking for PHP developers who can develop their webpage.

If you're planning to do an online business, your PHP programmer will be the first person to transfer your thinking onto the webpage. You should, therefore, employ developers from PHP to make your hypothetical idea a reality.

With this software programming language, PHP developers all-around can easily build website frameworks, web content management systems, web template systems, and various other web-based designs.

Some of the reasons why we need to outsource these developers are:

Not everyone is the best in each field, all of us have our specific skills and talents hence, PHP developers are also the best at what they do. The time and money spent on the training of the in house employees would be saved if the professional PHP developers are hired. Instead of multitasking, if the employees were to focus on what they’re good at it would increase productivity too.

The PHP developers would be much more professional than the in-house workers. It would lead to the seriousness of work. Hence, on-time delivery is guaranteed with hired PHP developers.

In addition to these benefits, you would also be able to track your project through every stage in constant communication with your online team. These advantages make it incredibly popular and smart to hire a PHP developer.

The PHP developers have in-depth knowledge of PHP, HTML and various frameworks in terms of technical capabilities. Hiring PHP developers are advised to give your website a professional look based on PHP.

Much of web success depends on the involvement of social media. The developer can add to your social networking pages a feature that explicitly redirects visitors. In addition, SEO experts also suggest better connections to the website's social network.

Just like a tailor stitches our dresses according to our preferences and is ready to make last-minute changes. A PHP developer will also be available at the nick of your call to make the website just the way you want it to be and have a customized solution for every problem.

Read also: Why & How to Hire Dedicated PHP Developer

At some point in your business, you’re going to have problems regarding your webpage due to the rapidly changing technology, instead of struggling with ideas like these and not being able to come up with an appropriate solution a PHP web developer could help us with our problems just like any technician would help us with the problems we face in our offices or any architect would help us with designing the structure of a building or any interior designer would help us with setting up our home. The PHP development company are hubs of workers who would help us overcome these problems and are always there.

Source by