Web Technology Experts Notes

Friday, 19 October 2012

Group_concat: its used to concatenate column values into a single string.

Create a table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Following are examples

select group_concat(name) as all_user where country='india'

List all the name separated by comma (,)
for example: arun,raj,suman,arun,ansh

select group_concat(name SEPARATOR '--') as all_user where country='india'

List all the name separated by double dash(--)
for example: arun--raj--suman--arun--ansh

select group_concat(name order by name asc) as all_user where country='india'

List all the name separated by comma (,) but order by name
for example: arun,arun,raj,suman,ansh

select group_concat(distinct name) as all_user where country='india'

List all the name separated by comma (,) but with distinc name only
for example: arun,raj,suman,ansh

Monday, 15 October 2012

Expressions are a common element of SQL statements( statements are select, delete and update).
they often occur in the where clause of select, delete & update.

For Example:
DELETE FROM users WHERE id=5;

Here id=5 are expressions.

Following are the different components of SQL Expression:

Numeric Expressions
Exact value Literal are: 11,12.33, 44.333
Approximate Literal are 1233E2, 44333E3
Operations on decimal value have precision upto 64 digits, currently its 30 character.
In Approximate, mantissa allows upto 53 bit of precision which is about 15 decimal digits.
String Expressions
String like 'sql' or mysql
Temporal Expressions
It include dates, times and datetime values like Select ‘2012-01-01’ + interval 10 day

Functions like current(), version(), avg(), sum()
Regular Express (Regex)

Sunday, 14 October 2012

MySQL Delete Table Example

Create a table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Delete record(s) from table

DELETE FROM users WHERE id=5;

It will delete a record where id is 5

Delete record from two table when they are in join.

DELETE users1,users2 FROM users1,users2,users3 WHERE tusers1.id=users2.id AND users2.id=users3.id

Keep in Mind following points..

If we specify the LOW_PRIORITY keyword in query, execution of the DELETE is delayed until no other clients are reading from the table.
For MyISAM tables, if we specify the QUICK keyword in query, then the storage engine will not merge index leaves during delete, which will speed up certain kind of delete.
If we specify the IGNORE causes MySQL to ignore all errors during the process of deleting rows.

Replace Table - Update Field, Move Records to another table MySQL

Create a table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

search for 'arun' in 'arun kumar' and replace with 'pardeep'

select replace('arun kumar','arun','pardeep')

It will print "pardeep kumar"

search for 'php' in company field and replace with 'php-tutorial-php'

UPDATE users SET company = REPLACE(company, 'php', 'php-tutorial-php');

Replace a table

REPLACE INTO T SELECT * FROM T;

Note:
If you run a replace on existing keys on table "users", and table "profile" references "users" with a forgein key constraint ON DELETE CASCADE,
If you replace table "users"
then table "users" will be updated - but table "profile" will be emptied due to the DELETE before INSERT.

mysql insert syntax - Add records from another table - Copy Table from one table to another

Create a table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Insert record - Way 1

insert into `users` set name='php tutorial'

Insert record - Way 2

INSERT INTO `users` (`id`, `name`, `image`, `company`, `created`, `modified`) VALUES (NULL, 'arun', NULL, 'php-tutorial', NULL, CURRENT_TIMESTAMP);

Add multiple records

INSERT INTO `mysql_certification`.`users` (`id`, `name`, `image`, `company`, `created`, `modified`) VALUES (NULL, 'arun', NULL, 'php-tutorial', NULL, CURRENT_TIMESTAMP), (NULL, 'arun', NULL, 'php-tutorial', NULL, CURRENT_TIMESTAMP);

Copying rows from one table to another

INSERT INTO `users`  (name, company) select name, company from users where id<5

Mysql Update Syntax - Update Multiple Records

How to create a new table?

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Following are different ways to update table.

update `users` set `company`='php tutorial';

update `users` set `company`='php tutorial',`name` ='myname'

update `users` set `company`='php tutorial',`name` ='myname' where id>100

You can also update multiple table in single query.

UPDATE users,profile SET users.company='php-tutorial-php' WHERE profile.id=users.id and profile.name='php';

Tuesday, 9 October 2012

PHP Interview Questions and Answers for fresher

Question: What is PHP?
Answer:

PHP is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.

Question: What is name of function that allows you to store session data in a database?

Answer: session_set_save_handler

Question: What is obj_start()?

Answer:It is used to initialize the object buffer, so that whole page first parsed instead of parsing in parts and send to browser.

Question: Why whitelists afford stronger protection against attacks than blacklists?

Answer: Blacklists words are some bad words (like s*x, f**k etc) that are not allowed to input by users into application. Except blacklists words, all words are allowed to input into the database.
whitelists words are those words that only allowed to input by user in application. Except Whitelists, all words removed while input the data into database.

Question: What is Session Register?

Answer: It is used to register a one or more global variable with current session. It session_start() was not called then it will call automatically when you call session_register;

Question: What are new Featues in PHP5?

Answer: Type hinting

Magic method and Magic constants

Standard PHP Libarary (SPL)

Namespaces

Access Modifier (Public, Privated & Protected)

Changes in Functions

Heredoc introduced

E_STRICT : warning for deprecated function

2nd option is optional in ternary operator

Question: List the Error constants in php?

Answer: E_ERROR, E_NOTICE, E_PARSE, E_WARNING, E_USER_WARNING, E_COMPILE_WARNING.

Question: What are the advantages of stored procedures, triggers, indexes in PHP?

Answer: Stored Procedure: It is set of sql command that can be compiled and stored in the server. Once this has been done, clients don't need to keep re-issuing the entire query. this improve the performance because the query has to be parsed only once.

Question: What is difference between cookie and session?

Answer: Session stored in server side and cookie stored in client side.
Session is logical object that store the object OR data and preserver on navigation of multiple page.
Cookie is stored temporary in browser (like mozilla, IE) and expired after close the browser.

Question: What is Persistent Cookie ?

Answer: Persistent cookie stored in browser and don't expired after close the browser. It retain the information until manually removed the cookie.

Question: Is overloading possible in PHP? How?

Answer: Yes, overloading is possible. It is possible through Magic method.

Question: Name of php function that Counts the number of occurrences of ever character.

Answer: count_chars

Question: What is cache?

Answer: It is high speed access protected area in a computer which is reserved for temporary storage of data. It can be divided into two parts.
A) Cache Memory - It is more fast access area(Static RAM).
B) Disk Cache - Cache stored in disk (Dynamic RA)

Question: How session is working?

Answer: When we create a session, it stored in server side but store the protected key in browser cookie.
That's why most of sites stop login functionality when you disable the cookie.

Question: What is PHPUnit?
Answer: It is framework, written in PHP used testing the code.

Question: How to Store Session in databases?.

Answer: To store the session in database, we have to get access whenever session is created, read, write, destroy. For this their is one function in php session_set_save_handler

bool session_set_save_handler ( callable $open , callable $close , callable $read , callable $write , callable $destroy , callable $gc )

Just pass the 6 function name, that will be call automatically whenever you start using session.

Question: Get the Images source from the html source?

Answer:

$htmlSource = ".......";
$imageArray = array();
$doc = new DOMDocument();
$doc->loadHTML( $htmlSource  );

$tags = $doc->getElementsByTagName('img');

foreach ($tags as $tag) {
       $imageArray[] = $tag->getAttribute('src');
}

Question: What is traits in PHP?
Answer: PHP5.4 implements a method of code reuse called Traits.

Question: Difference between Notice, warning and Error ?
Answer: NOTICE: this is a short message for saying what to do or what not to do. For Example, When you are trying to use undefined varaible

echo $noExist;

WARNING: occcurs at run time. Code execution continues. more serious as compare to warning. For Example, When you are trying to include a noexistfile

 echo include "notexist.php";

ERROR: this also occurs at run time, execution terminates. Its more serious to above both. For Example, When you are trying to use require a noexistfile

 echo require "notexist.php";

Question: How to set cookie
Answer: Use setcookie function to set the cookie. you can set the cookie name, value, expiration date, domain, secure, httponly.
http://www.web-technology-experts-notes.in/2012/10/setcookie.html

Question: What does a special set of tags <?= and ?> do in PHP?
Answer: The output is sent directly to the browser.

Question: What is default port of FTP?
Answer: 21

Question: What is default port of SFTP?
Answer: 22

Question: What is default port of SMTP?
Answer: 25

Question: What is default port of MYSQL?
Answer: 3306

Question: What is default port of HTTP?
Answer: 80

Question: How to set and get a constant value?
Answer:
/*set a value as constant*/
define("MAXSIZE", 100);

/*get a value of constant*/
echo MAXSIZE;
echo constant("MAXSIZE")

Question: What does do ob_start();

Answer: ob_start Function is used to turn on output buffering.The PHP output buffering will save all the server outputs to a string variable.it returns TRUE on success or FALSE on failure.If you are using ob_start, then you should also use ob_end_flush function for flushing the output buffer. You can also use a call-back function like ob_start("function_name");

Question: What is Difference JSON and JSONP

Answer: JSONP is JSON with padding, that is, you put a string at the beginning and a pair of parenthesis around.

//JSON format
{"name":"Rob Allen","id":5}
//JSONP format
func({"name":"Rob Allen","id":5});

Question: List out the predefined classes in PHP?
Answer: Following are some predefined class in PHP.

Directory
stdClass
__PHP_Incomplete_Class
exception
php_user_filter

Question: What is the difference between session_register and $_session?

Answer: If session_start() was not called before session_register() is called, an implicit call to session_start() with no parameters will be made. But $_SESSION requires session_start() before use.

session_register function returns boolean value and $_SESSION returns string value

session_register() function has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 6.0.0.

Question: How to Change the Session name?

Answer: session_name — Get and/or set the current session name.

Question: What is JSON?
Answer: Full form of JSON is JavaScript Object Notation. It is a lightweight data-interchange format, which is independent of operating system and languages. Its similar to XML, but better in performance like smallar and fast.

Question: How do you capture audio/video in PHP?
Answer: FFmpeg is a complete solution to record, convert and stream
audio and video.

Question: How to prevent form hijacking in PHP?
Answer: Use token system in form.

Question: What is the difference between mysql_fetch_object, mysql_fetch_array and mysl_fetch_row?
Answer:
mysql_fetch_array() - Fetch a result row as an associative array, a numeric array, or both
mysql_fetch_assoc() - Fetch a result row as an associative array
mysql_fetch_row() - Get a result row as an enumerated array
mysql_data_seek() - Move internal result pointer
mysql_query() - Send a MySQL query

mysql_fetch_object: Returns an object with properties that correspond to the fetched row and moves the internal data pointer ahead.

Question: What are the differences between require and include, include_once?
Answer:
File will not be included more than once. If we want to include a file once only and further calling of the file will be ignored then we have to use the PHP function include_once().

This will prevent problems with function redefinitions, variable value reassignments, etc.

Question: What is meant by nl2br()?
Answer: Returns string with '<br />' or '<br>' inserted before all newlines (\r\n, \n\r, \n and \r).

Question: How to set the response code in PHP?

http_response_code(404);

Question: How to get the response code in PHP?

echo http_response_code();//404

Thursday, 4 October 2012

Session Fixation

In this an attacker explicitly sets the session identifier of a session for a user. Typically in PHP it's done by giving them a url like http://www.mysite.com/index.php?session_name=session_id. Once the attacker gives the url to the client, the attack is the same as a session hijacking attack.
Default session_name is PHPSESSID
session_id is a unique string and it is in the range a-z A-Z 0-9 , (comma) and - (minus)!

The most crucial piece of information for an attacker is the session identifier, because this is required for any impersonation attack. There are three common methods used to obtain a valid session identifier:

Prediction: Guessing a valid session identifier. With PHP's native session mechanism, the session identifier is extremely random, and this is unlikely to be the weakest point.
Capture: Capturing a valid session identifier is the most common type of session attack, and there are numerous approaches for capturing session_id, because session identifiers are typically propagated in cookies or as GET variables.
Fixation: Fixation is the simplest method of obtaining a valid session identifier by using session_id() after session_start()

What to do
By default session_name is PHPSESSID, so this session name either from php.ini file OR with use of php function session_name. For example session_name('new_session_name')
Set session.use_trans_sid = 0 in your php.ini file. This will tell PHP not to include the identifier in the URL, and not to read the URL for identifiers.
Set session.use_only_cookies = 1 in your php.ini file. This will tell PHP to never use URLs with session identifiers.
Regenerate the session ID anytime the session's status changes. That means any of the following:
User authentication

Storing sensitive info in the session
Changing anything about the session

Session Hijacking in PHP

Session Hijacking is term where attackers hold of a session identifier and is able to send requests as if they were that user.
In particular, it is used to refer to the theft of a magic cookie used to authenticate a user to a remote server.
It has particular relevance to web developers, as the HTTP cookies used to maintain a session on many web sites can be easily stolen by an attacker using an intermediary computer or with access to the saved cookies on the victim's computer (see HTTP cookie theft).

How to prevent your data from Session Hijacking
1) In php.ini set session.hash_function = sha256 or session.hash_function = sha512.
2) In php.ini set session.hash_bits_per_character = 5
3) Add "user agent" (browser) in session & check each subsequent request.
4) Add IP Address in session & check each subsequent request.
5) Change the name of the session from the default PHPSESSID
6) In secure pages ask for reenter the password.

Subscribe to: Comments ( Atom )

Web Technology Experts Notes

Friday, 19 October 2012

Mysql-Group-Concat

Group_concat: its used to concatenate column values into a single string.

Following are examples

Monday, 15 October 2012

MySQL Expressions

Sunday, 14 October 2012

MySQL Delete Table Example

Keep in Mind following points..

Replace Table - Update Field, Move Records to another table MySQL

mysql insert syntax - Add records from another table - Copy Table from one table to another

Mysql Update Syntax - Update Multiple Records

Tuesday, 9 October 2012

PHP Interview Questions and Answers for fresher

Thursday, 4 October 2012

Session Fixation

Session Hijacking in PHP

Friday, 19 October 2012

Group_concat: its used to concatenate column values into a single string.

Following are examples

Monday, 15 October 2012

Best Related Posts are Following:

Sunday, 14 October 2012

Keep in Mind following points..

Best Related Posts are Following:

Best Related Posts are Following:

Best Related Posts are Following:

Best Related Posts are Following:

Tuesday, 9 October 2012

Best Related Posts are Following:

Thursday, 4 October 2012

Best Related Posts are Following:

Best Related Posts are Following: