6 Common PHP Security Issues And Their Remedies

Introduction

As you know, PHP is a very popular server side scripting language. According to W3Techs, more than 80% of the Web sites are based on PHP. This programming language is absolutely perfect for creating dynamic Web sites. PHP takes input from a stream containing text (the HTTP requests) and then outputs a result in the form of HTML, JSON, XML, image, audio, etc.. (the HTTP response).

Apart from the extensive usage, a report by National Vulnerability Database (NVD) indicates that 9% of vulnerabilities are related to PHP. That means that some programmers inadvertently leave loopholes in their code, so PHP sites become vulnerable.

Despite the PHP taint checking feature could be used to help detecting some types of security issues, there are many other security concerns that PHP developers should have, which are listed as follows.

PHP security issues

1. SQL Vulnerabilities

SQL injection is the most commonly reported security issue. It is mainly associated with those Web sites containing large code bases written a long time ago when developers were not so much security aware.

Through this kind of attacks, hackers may get access to databases associated with the PHP web sites. They may insert malicious code and modify or even delete your database. This kind of problem usually arises due to data validation and escaping loopholes left by PHP developers.

Examples

$query = "SELECT * FROM students  WHERE empname='David'";

The bbove query can be exploited as:

$query = "SELECT * FROM students WHERE empname='' or '1'";

The above query will return true and hence all the data from table students is returned. An attacker may alter the databases and the Web site may get crashed as the attackers gain administrative privileges.

Prevention

Before being processed by the application, the data should be validated. Invalid data should not be processed at all. Possibly valid data should be escaped before passing it to the database as query parameters. If possible use database extensions that support prepared queries like MySQLi or PDO.

Passwords must be hashed using the password_hash() function.

Technical details should be removed from error messages displayed to the users because smart hackers may get into the system using these details, like database names, user names and tables names.

An attacker specifically looks at error messages to get information such as database names, user names and table name, hence, you should disable error messages or you can create your own custom error messages.

You can also limit permissions of your application database user to make your database more secure. You can limit users access to database tables and views by using stored procedures and previously defined cursors. You can limit the privileges of the database user by preventing the use of keywords like drop, union, update and insert which can allow malicious modification of database.

2. Buffer Overflows

Usually, a buffer overflow problem is not caused directly by the code of interpreted languages like PHP. However the PHP engine is written in C. So buffer overflows may occur in PHP due to bugs in the C implementation of the PHP engine. Hence, it can be said that PHP applications are secure from overflows but the PHP engine itself is not.

PHP code does not allocate memory directly. It is the C code of the PHP engine that allocates and frees the necessary memory. A buffer overflow occurs in C code of the PHP engine that writes to memory beyond the boundaries of memory that was allocated.

Buffer overflows may cause the PHP engine to execute arbitrary code that can perform security exploits.

Since it happens at the level of the C code of the PHP engine, you cannot determine whether your PHP code may trigger buffer overflow vulnerabilities just looking at your PHP code.

You can however use PHP extensions like Suhosin that can alter the way PHP memory is allocated to detect many cases of buffer overflow occurrences and stop executing the PHP engine to avoid possible exploits.

3. XSS Exploits

The most usual form of Web site hacking is cross site scripting (XSS). Using this vulnerability, hackers force a site to perform certain actions. What hackers do is basically to inject a client side scripting code (JavaScript) mixed with submitted content, so that when a user visits a Web page with the submitted content, the malicious script gets downloaded automatically in his web browser and gets executed.

In this process, the malicious code usually gets saved in the database as if it was legitimate content. When a user opens the Web page, cookies and session identifiers may be stolen and sent to a third party site of the attacker. As a result of XSS flaws, the user may get redirected to a spammy Web site for instance.

XSS may also be used for user account hacking. When the attacker is able to steal the PHP session cookie value, he may be able to access to the user account as if it was the real user.

Prevention of XSS Exploits

XSS vulnerabilities can be avoided by properly encoding HTML using entities for <, >, ” and ‘. Escaping of HTML characters on online forums can also be avoided by using bbcodes usually offered there.

The htmlpecialchars() function can be helpful in this regard as it converts content automatically into HTML entities. It also converts single quotes by using ENT_QUOTES as second argument. The strip_tags() function also removes PHP and HTML tags from string.

4. Error Handling Problems

Another important area of concern is the error handling problems. Hackers may make some guesses about your software, PHP code, database tables and external programs. Such guesses may be used to exploit your system.

Detailed descriptions should be avoided as much as possible in error messages. You can structure your PHP code so that such error messages could sent to server’s error log instead of showing to the user. You can do that by adding these options to the php.ini configuration file:

log_errors=On
display_errors=off

5. Remote Administration Flaws

It is also recommended that you run remote administration tools, so that passwords and content can be protected.

Moreover, if you have remote access with administration rights via third party software then you should change the default credentials along with default administrative URL. It will be much safer if you can manage to have different Web server than public web server for the use of administrative tools.

6. Session And Cookie Hijacking

Session and cookies can not exploit the database or the web app but it can affect the user accounts. When the user contacts with the Web server a session may be started.

A session basically consists of time interval of interaction between the Web application and users which might be authenticated for making it more secure. Using PHP sessions, by default, the Web site stores in a file the user’s session data on the server and sends the session identifier to the browser as a cookie.

The attacker may try to obtain user’s session ID which is created the session is started for the first time for a given user accessing the site.

Prevention:

You can use the session_regenerate_id() function to change session IDs frequently. So if the user session identifier is stolen by somebody that intercepts the connection between the user browser and the server, that identifier will be invalid next time the user accesses again.

Revalidations of the user sensitive information like password can minimize the risk of hacking.

Such applications that handle sensitive information like debit and credit cards must be secured by using SSL so that session and cookie hacking can be avoided. Login or password change pages should also be accessible only via SSL.

Furthermore, avoid session identifiers and other cookies to be stollen using malicious JavaScript inject in the Web pages, for instance with cross-site scripting attacks, you can use HTTP-only cookies. These are cookies that the browser stores in on its side but JavaScript code does not have access to these cookies.

For cookies you can set the cookie like this:

setcookie('mycookie', 'some value', 0 ,"/", "", false , true);

For sessions you can set the session cookie parameters like this:

session_set_cookie_params (600 [, '/' , '' , false, true);

Or set the the session.cookie_httponly option in php.ini:

session.cookie_httponly = On

Conclusion

PHP security issues can be avoided by following certain guidelines and precautions while coding. If you are using managed cloud hosting services, like Cloudways, that I work for, you may be provided with security measures in order to make your Web site more secure.

If you liked this article, or have questions regarding security measures, post  a comment here.

5 Things You Should Check Now to Improve PHP Web Performance

We all know how financially important it is for your app’s server architecture to handle peaks of load. This article discusses 5 tips for improving PHP Web performance.

Primarily, you need to understand the key actions that are necessary to enhance the efficiency of your server-side PHP code. But: Why do you need to take those actions? If your application is running smoothly right now, is it worth the effort? Some actions require big investments. However, there are a lot of free resources available that can help you apply some easy changes.

The most important thing is performance data collection. If you want to improve something, you need to measure and compare the situation before and after. But what should you measure? I find that speed and memory usage are the most important, generally.For PHP, page load times are the most important thing to measure. There are some other issues you can take into account, such as network latency, and filesystem I/O. But problems here will fall into the speed and memory usage category, and here we can measure them easily.

Advice: You should be able to switch on/off your monitoring system as it may interfere with performance. You can slow your application down significantly if you flood the code with logs, but sometimes those logs may be the main decision point to take corrective actions.Find a happy medium and be careful.

You can use this code snippet to measure memory usage in PHP:

$time = microtime(TRUE);

$mem = memory_get_usage();

[the code you want to measure here]

print_r(array(

'memory'=> (memory_get_usage() -$mem) / (1024*1024),

'seconds'=>microtime(TRUE) -$time

));

Cache like there’s no tomorrow

This is not an original piece of advice. This advice probably appears in all performance checklists, which reflects how important it is. There are several tools to help you with this task, including the mythical Memcache or the new and powerful Varnish. Essentially, you must ask yourself if you really need to execute the PHP code over and over. If the information remains the same or maybe your user can afford to see one snapshot of the real status, caches can save you CPU cycles and give you extra speed. There are several types of caches. This example deals with a server-side cache.

function slowAndHeavyOperation() {

sleep(1);

returndate('d/m/Y H:i:s');

}

$item1 = slowAndHeavyOperation();

echo $item1;

This code will run for one second, due to the sleep function, to simulate one slow operation. Refactor this code to:

$memcache = new Memcache;

$memcache->connect('localhost', 11211);

function slowAndHeavyOperation() {

sleep(1);

returndate('d/m/Y H:i:s');

}

$item1 = $memcache->get('item');

if ($item1 === false) {

$item1=slowAndHeavyOperation();

$memcache->set('item', $item1);

}

echo $item1;

Now the script will take one second the first time, but it will take essentially no time when it runs additional times because you have cached the execution of the function. As you can see, it has one fee. Now the function will always return the same date instead of the current time. But Memcached allows you to set a TTL (Time To Live) in the data stored. With this feature, you can set one refresh policy to the cached data. Your outcomes are not really real-time, but the server will save a lot of resources, especially under heavy load and with a high number of concurrent users. See Memcached documentation herefor additional information.

Advice: Keep in mind that Memcache does not persist the data. If you restart Memcache, you will lose all data. Your application must be able to rebuild the cache if it is empty. In other words, your application must work with or without Memcached. Do not rely on the existence of data, especially in cloud environments.

Memcached gives you a simple and powerful mechanism to create server-side caches. You also can create more advanced caches. You can cache different parts of your site with a different TTL. For example, you may want to cache for your page header for two hours and your sidebar for ten minutes. In this case, you can use Varnish.

Varnish is a mix of cache and HTTP reverse proxy. Some people call these kinds of tools HTTP accelerators. Varnish is very flexible and customizable. Modern PHP frameworks, such as Symfony2, have integrated Varnish because of its popularity.

To review, caches can help us in three ways: First with our CPU/Memory requirements, and second, with the page load times and as a result, the SEO. The standard Google Analytics considers any web page load time over 1.5 seconds to be slow. It’s important to know that slow pages have SEO penalties so we cannot take it lightly.

Loops are evil

We habitually use loops. They are powerful programming tools, but they can frequently cause bottlenecks. One slow operation executed once is one problem, but if this sentence is inside a loop, the problem is magnified. So, are loops bad? No, of course not, but you need to assess your loops carefully, especially nested loops, to avoid possible problems.

Take the following code as an example:

<?php

// bad example

function expexiveOperation() {

sleep(1);

return"Hello";

}

for ($i=0; $i<100; $i++) {

$value=expexiveOperation();

echo$value;

}

This code works, but it is obvious that you are setting the same variable once per cycle.

<?php

// better example

function expexiveOperation() {

sleep(1);

return"Hello";

}

$value = expexiveOperation();

for ($i=0; $i<100; $i++) {

echo$value;

}

In this code, you can detect the problem and easily refactor. However, real life might not be this simple.

To detect performance problems, consider the following:

  • Detect big loops (for, foreach, …)
  • Do they iterate over a big amount of data?
  • Measure them.
  • Can you cache the operation inside the loop?

○If yes, what are you waiting for?

○If not, mark them as potentially dangerous and focus your inspections on them. Small performance problems in your code can be multiplied.

Basically, you must know clearly where are your big loops are and why. It is difficult to memorize all the source code of your applications, but you must be aware of the potentially expensive loops. Yes, I know. This recommendation seems to be written with micro-optimization in mind (like: cache the result of count()) but it isn’t. Sometimes I need to refactor old scripts with performance problems. I normally use the same pattern: Find loops with the profiler and refactor the heaviest.

We have one good friend here to help us with this job: The profiling tools. Xdebug and Zend Debugger allow us to create profiling reports. If we choose Xdebug we can also use Webgrind, a web front-end for Xdebug. Those reports can help us detect bottlenecks. Remember, a bottleneck is a problem, but a bottleneck iterated 10000 times is 10000x bigger. It seems obvious, but people tend to forget.

Queues are your friend

Do we really need to perform all the tasks inside the user request? Sometimes it’s necessary, but not always. Imagine, for example, that you need to send one email to a user when he/she submits an action. You can send this mail with a simple PHP script, but this action can take one second. If you wait until the end of the script, you will ensure that when the user sees the message “email sent” that the email has already been delivered. But is it really necessary? You can queue the action and free this one second from the user request. The email will be sent later and the user doesn’t need to wait until it has been sent. If the application is small, you can afford that. But if it scales, there could be a serious problem.

The amazing tool Gearman is a framework that allows you to create queues and parallel processing. Read the documentationfor more information. The main idea behind Gearman is simple. Instead of executing your actions inside your scripts, you can define “Workers” that the main script will call.

The following is an example of Gearman in action:

Imagine a simple script to add a watermark to one image:

<?php

$filename = "/path/to/img.jpg";

if (realpath(__FILE__) == realpath($filename)) {

exit();

}

$stringSize = 3;

$footerSize = ($stringSize==1) ? 12 : 15;

$footer = date('d/m/Y H:i:s');

list($width, $height, $image_type) = getimagesize($filename);

$im = imagecreatefromjpeg($filename);

imagefilledrectangle (

$im,

0,

$height,

$width,

$height-$footerSize, imagecolorallocate($im, 49, 49, 156));

imagestring($im,

$stringSize,

$width-(imagefontwidth($stringSize)*strlen($footer)) -2,

$height-$footerSize,

$footer,

imagecolorallocate($im, 255, 255, 255));

header( 'Content-Type: image/jpeg' );

imagejpeg($im);

Now, instead of doing it online, you can create a Worker:

<?php

$gmw = new GearmanWorker();

$gmw->addServer();

$gmw->addFunction("watermark", function($job) {

$workload=$job->workload();

$workload_size=$job->workloadSize();

list($filename, $footer) =json_decode($workload);

$footerSize=15;

list($width, $height, $image_type) =getimagesize($filename);

$im=imagecreatefromjpeg($filename);

imagefilledrectangle (

$im,

0,

$height,

$width,

$height-$footerSize, imagecolorallocate($im, 49, 49, 156));

imagestring($im,

$stringSize,

$width-(imagefontwidth($stringSize)*strlen($footer)) -2,

$height-$footerSize,

$footer,

imagecolorallocate($im, 255, 255, 255));

ob_start();

ob_implicit_flush(0);

imagepng($im);

$img=ob_get_contents();

ob_end_clean();

return$img;

});

while(1) {

$gmw->work();

}

And now the Gearman client in the main script:

<?php

$filename = "/path/to/img.jpg";

$footer = date('d/m/Y H:i:s');

$gmclient = new GearmanClient();

$gmclient->addServer();

$handle = $gmclient->do("watermark", json_encode(array($filename, $footer)));

if ($gmclient->returnCode() != GEARMAN_SUCCESS){

echo"Ups something wrong happen";

} else {

header( 'Content-Type: image/jpeg' );

echo$handle;

}

The coolest thing about Gearman is that you can start as many Workers as you need in the same host or in another one. The client application will remain the same. It allows you to scale out your applications depending on your needs. Imagine that your mailing application works fine but that you suddenly increase your users because of a great market opportunity. Your web server is enough to assume the load, but the mailing service is insufficient. Instead of upgrading your whole server, you can set up new Gearman nodes in a new host or even in the cloud. Simple, isn’t it?

Now a sort list with possible usages of Gearman:

  • Massive mailing systems
  • PDF generation
  • Image processing
  • Logs

Gearman is widely used within Web applications. For example, sites such as Grooveshark and Instagram use Gearman intensively. When you share one photo to Twitter or Facebook, Instagram uses a Gearman task queue to perform the task. They have about 200 Python Workers. That is another cool thing about Gearman: it is language agnostic. You can use a Python client with PHP workers, Java Workers, C client, Perl, Ruby, and so on.

If you have more specific needs, you can also check out ZeroMQ, which is a messaging library that allows you to design powerful communications systems.

Beware of Database Access

This is probably the main source of performance problems. If you like betting, you could say that the problem with the performance of a site is due to the database access, without inspecting the code. Most likely, you’re right. Database connections are expensive operations, especially with languages such as PHP, mainly because of the lack of connection pooling.

Moreover, the difference between a simple query using an index or not may be unbelievably big. Because I’m talking about differences here, it means that we need to measure. Remember the introduction: “You need to measure everything”? If you don’t measure, how would you know that you have improved the process?

The most important advice here is to check your database indexes. SQL queries using wrong indexes can significantly slow down an application’s performance.

Advice: Checking on database indexes cannot be done only once. You must take into account that as your data grows, indexing may change.

Another important tip is the usage of prepared statements. Why? The answer is simple. Let me show you one example:

$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'password');

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$field1 = uniqid();

$dbh->beginTransaction();

foreach (range(1, 5000, 1) as $i) {

$stmt=$dbh->prepare("UPDATE test.tbl1 set field1='{$field1}' where id=1");

$field1=$i;

$stmt->execute();

}

$dbh->commit();

And another one:

$dbh = new PDO('pgsql:dbname=pg1;host=localhost', 'user', 'password');

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$field1 = uniqid();

$dbh->beginTransaction();

$stmt = $dbh->prepare('UPDATE test.tbl1 set field1=:F1 where id=1');

foreach (range(1, 5000, 1) as $i) {

$field1=$i;

$stmt->execute(array('F1'=>$field1));

}

$dbh->commit();

Both work. The first one sends the SQL update as one string and executes it 5,000 times. The database needs to compile each update and execute it. The second one compiles it once and executes 5,000 times with different parameters. There is another great benefit from using prepared statements, which is to prevent SQL injections. But if you are talking about performance, you need to take it into account.

Death By Traffic

What happens if your application is suddenly serving thousands of concurrent users? Will your server be able to handle it? It’s not easy to answer this question at a glance. If you need to check it, you have two possible ways to do so.

One is to test with 1,000 or more users in your development environment. If you don’t have that many people, you need to use tools to automate this kind of operation. There are several tools. The open-source solution apacheab. can create connections to your server and load test simple pages.

Right now I’m using the free version of Load Tester from Web Performance, Inc. It can automate test cases and unlike apache ab, it generates load from your network or a cloud system, such as Amazon’s EC2.The free version can generate up to 1,000,000 concurrent users.

To run a test in apache ab you can use http://www.google.com/ as our test subject and then run apache ab with the following command:

ab -n 100 -c 10 http://www.google.com/

This command will create 100 connections to your server with one concurrency level of 10 connections at the same time. Let’s examine the output:

gonzalo@desktop:~$ ab -n 100 -c 10 http://www.google.com/

This is ApacheBench, Version 2.3 <$Revision: 655654 $>

Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/

Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking http://www.google.com (be patient)…..done

Server Software: gws

Server Hostname: http://www.google.com

Server Port: 80

Document Path: /

Document Length: 218 bytes

Concurrency Level: 10

Time taken for tests: 2.222 seconds

Complete requests: 100

Failed requests: 0

Write errors: 0

Non-2xx responses: 100

Total transferred: 98200 bytes

HTML transferred: 21800 bytes

Requests per second: 45.01 [#/sec] (mean)

Time per request: 222.174 [ms] (mean)

Time per request: 22.217 [ms] (mean, across all concurrent requests)

Transfer rate: 43.16 [Kbytes/sec] received

Connection Times (ms)

minmean[+/-sd] medianmax

Connect: 82 101 8.9 103 121

Processing: 90 117 8.5 118 144

Waiting: 90 117 8.5 118 144

Total: 171 218 13.2 218 266

Percentage of the requests served within a certain time (ms)

50%218

66%223

75%227

80%229

90%233

95%239

98%251

99%266

100%266 (longestrequest)

There are several very interesting results. Look at the Request per second, the Transfer rate, and the Time Taken for test. If you don’t want this raw output, you can save the outcome in a csv with:

ab -n 100 -c 10 -e test.csv http://www.google.com/

Don’t let your application die from success if you need to scale or work in high-performance situations.

Summary

If you want to improve your Web performance, you need to answer these questions:

  • How many database connections do I have in my application?
  • How much time does each select statement spend?
  • How many select statements do you have?
  • Are they inside loops?
  • Do I really need them? Can I cache them at least with a TTL?
  • Is it really necessary to perform my transactions (Inserts, Updates) online inside the user request?
  • Is it possible to queue them?
  • Does my server support big load conditions and a high number of concurrent users?
  • How much CPU does the application use per request?
  • How much memory does the application use per request?

As you can see, there are a lot of questions that you must answer. Maybe you started reading this post looking for the perfect solution. Sorry, but there are no silver bullets. You must answer those questions depending on your needs and take the corresponding actions according to your application. There are different tools at your disposal which I have listed above, but there are plenty more out there and plenty being created each day.

Extra Credit: Front End

This article discusses backend development (in other words, PHP code). We, as developers, understand the difference between Frontend (JavaScript, CSS, HTML, …) and Backend (PHP, Databases, …), but the user doesn’t. The user only perceives the time between his click and the browser’s response. It is important to know that. Here, firebug or Chrome’s developer tools are our friends.

Imagine this simple script:

<?php

// our amazing application

?>

<html>

<head>





$(document).ready(function() {

console.log("Hello")

});



</head>

<body>

<imgsrc="http://placekitten.com/200/300"alt="img">

<imgsrc="http://placekitten.com/100/100"alt="img">

<imgsrc="http://placekitten.com/200/200"alt="img">

</body>

</html>

As you can see, the entire amount of time is not simply the application running the PHP script. We need to add the time that the browser takes to load and render all external resources, images, stylesheets, JavaScript, etc.

You can optimize the performance of your Backend by 90%, but you must realize that the Backend time is only 10% of the whole request script time.

Stored Procedures in MySQL and PHP

Put simply, a Stored Procedure (“SP”) is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages.

In this tutorial, we will see how to create an SP in MySQL and execute it in MySQL server and in PHP.

Note: We are not going to cover the full aspect of the SP here. The official MySQL document should always be the place for reference.

SP are also available in other common database servers (Postgre for example) so what we will discuss here can be applied to those as well.

Why Stored Procedures are recommended

Most of us are quite familiar with the normal setup to build a database application: creating database, creating tables, set up indexes, CRUD the data, issue queries from the client side and do further processing if necessary.

That workflow works fine in most cases but there is one important aspect of database programming missing: the Stored Procedure.

There are at least four advantages I can think of to use an SP in a database application.

Firstly, it reduces the network traffic and overhead. In a typical PHP database web application, there are four layers:

  • The client layer, which is normally a web browser. It receives user interactions and presents the data in a UI.
  • The web server layer, which handles and dispatches user requests and sends back responses to the client layer.
  • The PHP layer, which handles all PHP interpretation, does the application logic and generates the PHP part of response.
  • The database layer, which handles all database queries, including but not limited to a SELECT query, an INSERT statement, etc.

In a typical environment, these layers will most likely not reside on one single machine, maybe not even in one network, for larger applications.

Although network speed has tremendously increased in the past few years, it is still the slowest and most unreliable compared to other ways of transferring the data (CPU cache, memory, hard disk, etc). So, to save bandwidth and increase robustness, it is sometimes a good idea to have more processing and logic done on the server side (in particular, the MySQL server) and have less data transferred through the network.

Secondly, it improves the performance. SP is stored and run directly in the MySQL server. It can be pre-compiled and analyzed by the database server. This is quite different from issuing the same query from the client side, where the query will be parsed by database drivers, analyzed and optimized (if possible) every time the query statement is called. This is somehow quite like the interpreted language execution (at the client end) and the compiled language execution (at the database server end). And we know a compiled program will run faster.

Third, Write Once and Execute Anywhere. SQL is standard and purely 100% platform independent. It only relies on the database server. Consider how many different languages/libs there are that we can use to deal with the database. It increases efficiency to put the data retrieving and processing at the server end instead of writing the same processing logic in a different syntax provided by all these languages/libs, if the data processing logic is so commonly used.

Last but not least, SP is a fundamental aspect of database security.

Let’s consider a simple database setup. In a human resource information system (HRIS), it is reasonable to assume that there exists a table holding the salary information of each employee. An HR employee should have the right to grab some figures out of this table: total salary, average salary, etc but this employee should not see the detailed salary of each employee as this information will be too sensitive and should only be available to a few.

We know MySQL has a comprehensive privilege control. In this case, it is obvious that we can’t even grant SELECT privilege to this HR employee (which, if we do, means he/she can see the detailed salary of everyone). But if he/she can’t access the salary table, how can this employee get the aggregation information related to salary? How can we allow the employee to grab that information without compromising the HR policy?

The answer is using a Stored Procedure that returns the required information and grants that employee the EXECUTE privilege. (For a detailed list and explanation of MySQL privileges, please consult the official documentation. The link here is for MySQL 5.6. Please replace 5.6 with the version you are using.)

SP is now a bridge, bridging the user (our HR employee) and the table (salary), to which the user has no direct access.

That’s it! With SP, we can get the user to accomplish the task without compromising the database security (and HR policy)!

Drawbacks of using Stored Procedures

After naming all the advantages of using an SP, we need to be clear about some of the drawbacks and see if there are ways to improve.

  • No version control on SP itself. When an SP is modified, it is modified, no historical trails can be kept in the server side. It may create some frustrations when the user would like to rollback a change. My suggestion is to write the SP in your client side and put it under version control. When the SP is ready, it is easy to copy the code into, say MySQL Workbench and create it at the server side. By doing so, we can have some degree of version control.
  • No easy way to “synchronize” the changes applied and force everyone to use the latest version, in particular, when each of the team member has his/her own local database for development and testing purposes. Version control may be the solution but still requires manual intervention by updating the local copy of the SP in the local db server. Another way is to use “mocking”. The team members can be divided so that at least one person will focus on the maintenance of the SP and the implementation of the calling to SP in the code. All others that need the results from the SP can develop and test their portion using mocking objects, i.e, always assuming the “faked” call to the SP will return a desired result. In a later stage, merging can be done to discard the mocking code.
  • Hard to backup/export. The SP is on the server side. Regular developers will only have basic privileges (SELECT, EXECUTE, etc) and no admin rights to backup and export. In a way, I won’t call it a drawback but rather a fundamental aspect of db security. There is no way, and it is not recommended to get around this. It is suggested that, in a team, a dedicated DB admin will be appointed to do such jobs. A regular db backup can also serve the backup/export (and import) purpose.

Creating a Stored Procedure in MySQL

As SPs are stored in the server, it is recommended to create the SP directly in the server, i.e., not by using PHP or other programming languages to issue SQL commands to do so.

Let’s see how to create the SP in MySQL server, create a user and apply privileges and run (as that user) the SP to see if the result is correct. In my working environment, I am using MySQL Workbench. Other tools are available (PHPMyAdmin for example) so feel free to use the tools that best fit you.

Assume we have a table like this:

CREATE TABLE `salary` (
  `empid` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And for the HR employee that needs to get the aggregated information on salary (average, max, min, etc) from that table, we first create a user 'tr' like this:

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

and for this user, we only grant EXECUTE privilege to the schema where the salary table resides:

grant execute on hris.*  to tr@`%`

We can verify the necessary privilege is granted by visiting the “Users and Privileges” in MySQL Bench:

Now let’s create the SP like this:

DELIMITER $$

CREATE PROCEDURE `avg_sal`(out avg_sal decimal)
BEGIN
    select avg(sal) into avg_sal from salary;

END

NOTE: All the above operations will require an admin role in the MySQL server.

After issuing the command in MySQL Workbench, the avg_sal SP will be created and ready to be called. It will return the average salary of table salary.

To test if the user tr can actually run the SP but should not be able to access the salary table, we can switch the role by logging into the MySQL server using user tr. It can be done by creating another connection in MySQL Workbench using a different user/password pair.

After logging in as tr, the first thing we will notice is that the user will not be able to see any tables and can only see the SP:

It is clear that the user tr won’t be able to select anything from any table (thus unable to see the detailed salary number of the salary table) but he/she is able to execute the SP we just created and get the average salary of the company:

call avg_sal(@out);
select @out;

The average salary will be displayed.

We have so far done all the preparation to create a user, grant privileges, create a SP and test the running of the SP. Next we will show how to call that SP from within PHP.

Calling a Stored Procedure from PHP

With PDO, calling an SP is straightforward. The PHP code is as follows:

$dbms = 'mysql';

//Replace the below connection parameters to fit your environment
$host = '192.168.1.8'; 
$db = 'hris';
$user = 'tr';
$pass = 'mypass';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$q=$cn->exec('call avg_sal(@out)');
$res=$cn->query('select @out')->fetchAll();
print_r($res);

The $res will contain the average salary of the table salary. The user can further process the output with PHP now.

Conclusion

In this article, we reviewed the long-forgotten component in MySQL database: the Stored Procedure. The advantages to use an SP are obvious and let me re-emphasize: Stored Procedures allow us to apply stronger database access control to certain data to fit in with the business requirement.

We also illustrated the basic steps to create Stored Procedures, create a user and assign a privilege, and how to call it in PHP.

This article does not cover the full scope of Stored Procedures. Some important aspects like input/output parameters, control statement, cursors, the complete syntax, etc are not discussed in this short article.

If you feel interested, please leave your comments here and we will be glad to bring more in-depth articles about this useful and powerful aspect of MySQL.

What is PDO?

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

PDO ships with PHP 5.1, and is available as a PECL extension for PHP 5.0; PDO requires the new OO features in the core of PHP 5, and so will not run with earlier versions of PHP.

Difference between overloading and overriding in php with Example

Overloading: In Real world, overloading means assigning some extra stuff to someone. As as in real world Overloading in PHP means calling extra functions.
Example of Overloading:

class testclass {
    public $_data;
    public function __get($name) {
        echo "Getting '$name'\n ";
        return $this->data[$name];
    }
}

$obj = new testclass();
/** Magic method Example * */
echo $obj->a; //As __get function called - Overloading example 2
/** Magic method Example * */

We can do overloading with magic methods and are following:
__construct(), __destruct(), __call(), __callStatic(), __get(), __set(), __isset(), __unset(), __sleep(), __wakeup(), __toString(), __invoke(), __set_state(), __clone() and __debugInfo()

Overriding: In Real world, Overriding means changing the parental behaviour in Child. As as in real world Overriding in PHP means calling of child function instead of parent function, both class’s function have same name.
Example of Overriding:

class parentclass {
    function name() {
        return 'Parent';
    }
}

class childclass extends parentclass {
    function name() {
        return 'Child';
    }
}

$obj = new childclass();
echo $obj->name();//It called child function instead of parent parent functi

OOP Interview Questions and Answers

What is Object Oriented Programming?
Object-oriented programming (OOP) is a programming language model organized around objects rather than actions;
Objects are instances of classes, are used to interact with one another.

Following are few examples of object-oriented programming languages
PHP, C++, Objective-C, Smalltalk, C#, Perl, Python, Ruby.

The goals of object-oriented programming are:

  1. Increased understanding.
  2. Ease of maintenance.
  3. Ease of evolution.

 

What is data modeling?
In class, we create multiple get/set function to get and set the data through the protected functions known as Data Modeling.

class dataModel {    
    public function __set( $key, $value ) {
        $this->$key = $value;
    } 
}

Following are the benefits of Data Modeling

  1. It is very fast.
  2. Smart way to  manipulation on data
  3. No extra layer of logic
  4. Really flexible to be modeled per need
  5. Setters can explicitly define what data can be loaded into the object

What is difference between class and interface?
1) Interfaces do not contain business logic
2)You must extend interface to use.
3) You can’t create object of interface.

How Session – cookie works in PHP?
When a website open in new client machine(Browser), new sessionId is created and stored in php server and in client machine (In cookie).
All data is store in PHP Server and cookie only have sessionId. When client send sessionId with request to the server, then server fetch the data corresponsing to that sessionId and retun to the browser.

What are some of the big changes PHP has gone through in the past few years?
5.1 added PDO
5.3 – added namespace support

What is Polymorphism?
It is simply “One thing, can use in different forms”
For example, One car (class) can extend two classes (hond & Alta)

How to load classes in PHP.
We can load a class with the use of “autoload” class.
If we want to change from default function autoload to testautload function.
we can do this with “spl_autoload_register”

spl_autoload_register('kumar');



How to call parent constructor?

parent::__construct()

Are Parent constructors called implicitly when create an object of class?
No, Parent constructors are not called implicitly It must call this explicitly. But If Child constructors is missing then parent constructor called implicitly.



What happen, If constructor is defined as private OR protected.
If constructor declared as private, PHP through the following fatal error when create object. Fatal error: Call to private BaseClass::__construct() from invalid context in. If constructor declared as private, PHP through the following fatal error when create object. Fatal error: Call to protected BaseClass::__construct() from invalid context in



What happen, If New-Style constructor & old-style constructor are defined. Which one will be called.
New-Style constructor will called. But if New-Style constructor is missing, old style constructor will called.

What are different visibility of method/property?
There are 3 types of visibility of method & property and are following
Public: Can be accessed from same class method, child class and from outside of class.
Protected : Can be accessed from same class method, child class.
Private: Can be accessed from same class method only.

class TestClass
{
    public $public = 'Public';
    protected $protected = 'Protected';
    private $private = 'Private';

    function printValue()
    {
        echo $this->public;
        echo $this->protected;
        echo $this->private;
    }
}

$obj = new TestClass();
echo $obj->public; // Works
echo $obj->protected; // Fatal error: Cannot access protected property TestClass::$protected in
echo $obj->private; // Fatal error: Cannot access private property TestClass::$private in C:\wamp\www\arun\class\class.php on line 20
$obj->printValue(); // Shows Public, Protected and Private

What is Scope Resolution Operator?
The Scope Resolution Operator (also called Paamayim Nekudotayim) is double colon that allows access to static, constant, and overridden properties or methods of a class. Following are different uses Access to static
Acess the constant
Access the overridden properties of parent class
Access the overridden methods of a parent class

What is Static Keyword in PHP?

  • If we declare a Method or Class Property as static, then we can access that without use of instantiation of the class.
  • Static Method are faster than Normal method.
  • $this is not available within Static Method.
  • Static properties cannot be accessed through the object(i.e arrow operator)
  • Calling non-static methods with Scope Resolution operator, generates an E_STRICT level warning.
  • Static properties may only be initialized using a literal or constant value.
  • Static properties/Normal properties Can’t be initialized using expressions value.

 

class StaticClass
{
    public static $staticValue = 'foo';

    public function staticValue() {
        return self::$my_static;
    }
}
echo StaticClass::$staticValue;





What is Abstraction in PHP?

  • Abstraction are defined using the keyword abstract .
  • PHP 5 introduces abstract classes and methods. Classes defined as abstract may not be instantiated (create object).
  • To extend the Abstract class, extends operator is used.
  • You can inherit only one abstract class at one time extending.
  • Any class that contains one abstract method must also be declare as abstract. Methods defined as abstract simply declare the method’s signature, they can’t define the implementation.
  • All methods marked as abstract in the parent’s class, declaration must be defined by the child.
  • additionally, these methods must be defined with the same (or a less restricted) visibility (Public,Protected & private).
  • Type hint & number of parameter must be match between parent & child class.

What is Interface in PHP?

  • Interfaces are defined using the interface keyword.
  • All methods declared in an interface must be public. Classes defined as Interface may not be instantiated(create object).
  • To extend the interface class, implements operator is used.
  • You can inherit number of interface class at the time of extending and number of abstract class separated by comma.
  • All methods in the interface must be implemented within a child class; failure to do so will result in a fatal error.
  • Interfaces can be extended like classes using the extends operator.
  • The class implementing the interface must use the exact same method signatures as are defined in the interface. Not doing so will result in a fatal error.
  • Type hint & number of parameter must be match.

What is Traits in PHP?

  1. Traits are a mechanism for code reuse in single inheritance.
  2. A Trait is similar to a class, but only intended to group functionality in a fine-grained and consistent way.
  3. It is not possible to instantiate a Trait but addition to traditional inheritance. It is intended to reduce some limitations of single inheritance to reuse sets of methods freely in several independent classes living in different class hierarchies.
  4. Multiple Traits can be inserted into a class by listing them in the use statement, separated by commas(,).
  5. If two Traits insert a method with the same name, a fatal error is produced.Example of Traits
class BaseClass{
    function getReturnType() {
        return 'BaseClass';
    }
}
trait traitSample {
    function getReturnType() {
        echo "TraitSample:";
        parent::getReturnType();
    }    
}

class Class1 extends BaseClass {
    use traitSample;   
}

$obj1 = new Class1();
$obj1->getReturnType();//TraitSample:BaseClass

What is Overloading?
It is dynamically create method / properties and performed by magic methods. Overloading method / properties are invoked when interacting with properties or methods that have not been declared or are not visible in the current scope, Means we you are calling a function which is not exist. None of the arguments of these magic methods can be passed by reference.
In PHP, Overloading is possible http://200-530.blogspot.in/2013/04/oop-magic-methods.html

What is Object Iteration?
PHP provides a way for objects to be iterate through a list of items, for this we can use foreach. Only visible properties will be listed.

class TestClass{
    public $public='PublicVal';
    protected $protected='ProtectedVal';
    private $private='PrivateVal';
    
    function myfunc() {
        return 'func';
    }
    
    function iterateVisible(){
        foreach($this as $key => $value) {
           print "$key => $value\n";
       }
    }
}

$obj=new TestClass();
$obj->iterateVisible();

What is Final Keyword in PHP?
PHP introduces the final keyword, which prevents child classes from overriding a method by prefixing the definition with final.
If the class itself is being defined final then it cannot be extended. If the function itself is being defined final then it cannot be extended.

What is Serialize function in php?
It return string containing a byte-stream representation of any value that can be stored in PHP.



Comparing Objects?
When using the comparison operator (==), object variables are compared in a simple manner, namely: Two object instances are equal if they have the same attributes and values, and are instances of the same class.
When using the identity operator (===), object variables are identical if and only if they refer to the same instance of the same class



What is UML?
UML stands for Unified Modeling Language.
You can do following things with UML

  • Manage project complexity.
  • create database schema.
  • Produce reports.


What are Properties of Object Oriented Systems?

  • Inheritance
  • Encapsulation of data
  • Extensibility of existing data types and classes
  • Support for complex data types
  • Aggregation
  • Association

Object-Oriented Programming Concepts in PHP – Part 2

In the previous article of Object Oriented programming concepts in PHP, we learned to create a class, define objects for the class and create methods. We also learned about different topics of object-oriented programming like inheritance, interface, and abstraction.

In this article we will deal with php’s Magic methods. Php classes are some predefined functions. Magic methods starts with the prefix __, for example __call, __get, __set. There are various magic methods in php. Here we will discuss some of the following magic methods of php which will be use in object oriented programming. First of, let us review some important magic methods with short description:
Description_Magical_Methods

Magic Methods:

The function names __construct(), __destruct(), __call(), __callStatic(), __get(), __set(), __isset(), __unset(), __sleep(), __wakeup(), __toString(),__invoke(), __set_state(), __clone() and __debugInfo() are magical in PHP classes.

We cannot have functions with these names in any of our classes unless we want the magic functionality associated with them.

Now Let us discuss the important Magical Methods,

Constructors:

A constructor is the first function to be called even if n number of functions are there in the class. Constructor need not be initialized and called explicitly. It is automatically called when the object is created with the help of the new operator.

The constructor allows sending the parameter to the class when the object is created.

Examples for the constructor:
name =$animal_name;
}
}
$animal = new Animal("Indian Tiger");
echo $animal->name;
?>
view raw
Animal.php hosted with ❤ by GitHub

Output:

Indian Tiger

An example of constructor in INHERITANCE:

view raw

Output:

Calling parent class constructor

Calling parent class constructor

Calling child class constructor

Destructors:

__destruct is the keyword for the destructor. A destructor is called automatically when the object is destroyed. The destructor is automatically called by the garbage collector when the memory allocated for the variable or the object holding the resource is no longer needed.

Example:
name = "object";
}
function __destruct() {
echo "Calling Destructor " . $this->name . "\n";
}
}
$obj = new Destructexample();
?>

Output:

Calling the constructor

Calling Destructor object

__get():

__get() is utilized for reading data from inaccessible properties.

It accepts one argument, which is the name of the property.

It should return a value, which will be treated as the value of the property.

Example:
Age;
?>

Note that our student class has $Age commented out, and we attempt to print out the Age value of $obj. When this script is called, $obj is found to not to have an $Age variable, so __get() is called for the Student class, which prints out the name of the property that was requested – it gets passed in as the first parameter to __get(). If you try uncommenting the public $Age; line, you will see __get()is no longer called, as it is only called when the script attempts to read a class variable that does not exist.

__set():

__set() is run when writing data to inaccessible properties.

It accepts two arguments, which are the name of the property and the value.

For Example:
Name = $Name;
}
public function __set($var, $val) {
GLOBAL $db;
mysqli_query($db, "UPDATE {$this->Name} SET $var = '$val';");
}
// public $mailid = 'foo@bar.com';
}
$systemvars = new mytable("systemvars");
$systemvars->mailid = 'abc@xyz.net';
?>

In that script, $mailid is commented out and therefore does not exist in the mytable class. As a result, when $mailid is set on the last line, __set() is called, with the name of the variable being set and the value it is being set to be passed in as parameters one and two respectively. This is used to construct an SQL query in conjunction with the table name passed in through the constructor.

__toString():

The __toString() method is called when code attempts to treat an object like a string.

It accepts no arguments and should return a string.

This allows us to define how the object will be represented.

For Example:
x = $x;
}
public function __toString()
{
return $this->x;
}
}
$obj = new Strings('Hello, how r u');
echo $obj;

Output:

Hello, how r u

Cloning Objects:

Object cloning is the act of making a copy of an object.When the object is created with the help of new keyword and assigned the object created to a new variable the object reference will be copied but not the value stored in that object. And now if we want to change the value of the object created the copied object variable will also be affected. Similarly, if we change the copied object, the created object data will be affected. So to solve this problem we will use the concept of cloning

For example Copy of the object
class test {

public $a;

private $b;

function __construct($a, $b) {

$this->a = $a;

$this->b = $b;

}

}

$a = new test(“arun”, “technology”);

$b = $a; // copy of the object.

$a->a = “no arun”;

print_r($a);

print_r($b);

Output:

test object
(
[a] => no arun
[b:test:private] => technology
)

test object
(
[a] => no arun
[b:test:private] => technology
)

test object
(
[a] => arun
[b:test:private] => technology
)

Implementation of clone object in PHP:

Object cloning can be implemented by using clone keyword.

Example: Implementation of Object Cloning in PHP

Output:
a = $a;
$this->b = $b;
}
}
$a = new test("arun" , "technology");
$b = $a; //Copy of the object
$c = clone $a;
$a->a = "no arun";
print_r($a);
print_r($b);
print_r($c);
?>

test object

(

[a] => no arun

[b:test:private] => technology

)

test object

(

[a] => no arun

[b:test:private] => technology

)

test object

(

[a] => arun

[b:test:private] => technology

)

Object cloning with __clone magic method:

Suppose we need to change the value of property in clone object.The behavior of the clone object has changed by using magic method __clone in php.

Example: Object cloning with magic method __clone
a = $a;
$this->b = $b;
}
function __clone()
{
$this->a = "c";
}
}
$a = new test("arun" , "technology");
$b = $a; //Copy of the object
$c = clone $a;
$a->a = "no arun";
print_r($a);
print_r($b);
print_r($c);
?>

Output:

test Object

(
[a] => no arun
[b:test:private] => technology
)

test Object
(
[a] => no arun
[b:test:private] => technology
)

test Object
(
[a] => c
[b:test:private] => technology
}

In this article we explained the important concepts of PHP OOP called Magical Methods.The PHP magic methods are used to react to different events and scenarios that your object might find itself in. Each of these magic methods are triggered automatically, so in essence, you are just defining what should happen under those circumstances. Hopefully each of the examples I’ve presented in this blog will help to you.