Connecting to a database with PHP

Connecting to a database with PHP

Install these packages:

#apt-get install apache2
#apt-get install mysql
#apt-get install php
#apt-get install php5-mysql

Create a test user, password and database

At the sql server, Log into mysql:

#mysql -u root -p

Issue the following commands to create a user “test” and a password “password”:

CREATE USER ‘test’@’localhost’ IDENTIFIED BY ‘password’;
CREATE USER ‘test’@’%’ IDENTIFIED BY ‘password’;

GRANT ALL ON *.* TO ‘test’@’localhost’;
GRANT ALL ON *.* TO ‘test’@’%’;

CREATE DATABASE instruments

 

Exit mysql:

q

Log back in as the user you just created, attaching to the new database:

mysql -u test -p instruments

Execute a

s

to see the status. Verify the user and database.

Test PHP Functionality:

Create a file named “something”.php and insert the following text:

<?php echo ‘hello world’.time();
/* <?php echo ‘mysqli_connect(); print_r(mysqli_query(‘select now()’)) ; ?> */
?>

Place this file in the /var/www directory

Open a browser and point to that file:

http://<your server>”something”.php

You should see hello world and the date.

To test your connection to the database via PHP:

Create a file with the following text and name it “something”.php

Edit the line “$db = mysql_connect(“206.207.94.34″,”test”,”password”);” to reflect your server & user.

<?php
$db = mysql_connect(“206.207.94.34″,”test”,”password”);
if (!$db) {
die(“Database connection failed miserably: ” . mysql_error());
}
else

die(“Database Success!!!: ” . mysql_error());
$db_select = mysql_select_db(“instruments”,$db);
if (!$db_select) {
die(“Database selection also failed miserably: ” . mysql_error());
}
?>
<html>
<head>
<title>Step 3</title>
</head>
<body>
<?php
$result = mysql_query(“SELECT * FROM mytable”, $db);
if (!$result) {
die(“Database query failed: ” . mysql_error());
}
?>
</body>
</html>

Place this file in the /var/www directory

Open a browser and point to that file:

http://<your server>”something.php

Success!!!

HANDY MYSQL COMMANDS:

Note that all text commands must be first on line and end with ‘;’
? (?) Synonym for `help’.
clear (c) Clear the current input statement.
connect (r) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set statement delimiter.
edit (e) Edit command with $EDITOR.
ego (G) Send command to mysql server, display result vertically.
exit (q) Exit mysql. Same as quit.
go (g) Send command to mysql server.
help (h) Display this help.
nopager (n) Disable pager, print to stdout.
notee (t) Don’t write into outfile.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
print (p) Print current command.
prompt (R) Change your mysql prompt.
quit (q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file. Takes a file name as an argument.
status (s) Get status information from the server.
system (!) Execute a system shell command.
tee (T) Set outfile [to_outfile]. Append everything into given outfile.
use (u) Use another database. Takes database name as argument.
charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (W) Show warnings after every statement.
nowarning (w) Don’t show warnings after every statement.

For server side help, type ‘help contents’

Matt Long
01/27/2015

Bulk Domain NS, MX and A record lookup tool

Summary: We have two tools to help you lookup information on domains quickly

Bulk Domain NS, MX and A record lookup tool

Occassionally, we come across some sort of project in which we have to work through a list of multiple domain names and make some sort of changes.

In some cases we simply have to update contact records, in other cases we have to determine ownership, hosting and mail setups so we can assist with an ownership transfer.

There are a plethora of domain tools out there which help one at a time, But we were hard pressed to find a tool that could do a bulk lookup of multiple domains with table based out put.

 

So, we built the toolBulk Domain NS, MX and A record lookup tool

https://www.matraex.com/quick-domain-research.php

This tool has the

  • A records for the root domain (@) and the (www) domain.
  • MX records for the root domain
  • NS records for the root domain

This tool was thrown together quickly to help us identify whether an OLD but active nameserver, which had dozens of domain names on it,  was actually being used for the domains.

We were able to delete more than 20 domains cluttering up the DNS entries.

Additionally we were able to clean up associated webservers that had not been cleaned of hosting accounts after a client left the account.

Some future ideas which will make their way in next time:

  • Display whois information for the domain
  • Optionally group the domains based on which name servers, whois records or www C class they are hosted at

Bulk Domain NS, MX and A record lookup tool


Update 11/28/2015 by Michael Blood

Since this original post,  we have added several new features including the ability to upload a file with a large batch upload, and download a CSV file with the results.  You can see all of the details in this Enhanced Bulk Domain NS, MX and A record lookup tool post.

 

 

 

 

 

 

 

PHP to reset all primary key sequences in your postgresql database

PHP to reset all primary key sequences in your postgresql database

Use the following php code t reset all of the primary key sequences with the max(id) currently in the db.

We use wrapper functions db_query (which returns an array from the db when a select statement is run)  and db_exec()  which runs an update or insert command against the db.

[code language=”php”]$sql = &quot;SELECT t.relname as related_table,
a.attname as related_column,
s.relname as sequence_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid
JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid
JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
JOIN pg_namespace n ON n.oid = s.relnamespace
WHERE s.relkind = ‘S’
AND n.nspname = ‘public’&quot;;
$qry = db_query($sql);

foreach($qry as $row)
{
$outsql = &quot;select setval(‘$row[sequence_name]’,(select max($row[related_column]) from $row[related_table]))&quot;;
db_exec($outsql);
}[/code]

 

Fixed Hacked Site – PHP injection

Fixed Hacked Site – PHP injection

Today a customer called me about a PHP website that was popping up viruses all over the place.

I loaded up the site and there it was, the page was immediately redirected to a spyware / virus type site that tried to convince me to download their software to fix a problem. Since I knew better I carefully answered the browser prompts to make sure I closed out and left the page without opening anything malicious.
Then I went back to the page that had the problem and tried to load it again. But the problem was GONE!

After a bit more investigation I found that the people who wrote the virus dropped a cookie on my machine and made sure they allowed me back in the site. I am sure this trick helps them to keep the virus on a site for longer because the site administrators may not recognize it as an on going problem (or even a problem that their site caused).

In digging I found that each PHP page on the site had some PHP code added to the top of it.
something like

This was on a single line at the top of the file and even the administrator who had noticed the odd code at the top passed over it not thinking it was malicious.
However, the text inside the encoded string was VERY malicious. I decoded it and found several PHP functions and additional encoded strings.

I decided it wasnt worth figuring out what all they did with the code but instead decided to just clean it up. I assumed that the code probably helped replicate itself by checking that ALL other PHP pages on the site also had the same code in them. So if someone removed the code and then the code was run on another page it put itself back where you removed it.

Anyway, pretty sophisticated but it was easy for me to find the problem just opened and looked at the PHP file and saw code that shouldnt have been there.

A cool way that I found where the problem was before even opening the PHP file was to use HTTPWatch to see which exact files were downloaded from which site in the browser. I use the free version of the softwar and it has met all my needs so far. It is similar to firebug in FireFox.

 

Linux System Discovery

Linux System Discovery

Over the last couple of weeks I have been working on doing some in depth “System Discovery” work for a client.

The client came to us after a major employee restructuring,  during which they lost ALL of the technical knowledge of their network.
The potentially devastating business move on their part turned into a very intriguing challenge for me.

They asked me to come in and document what service each of their 3 Linux servers.
As I dug in I found that their network had some very unique, intelligent solutions:

  • A reliable production network
  • Thin Client Linux printing stations,  remotely connected via VPN
  • Several Object Oriented PHP based web applications

Several open source products had been combined to create robust solutions

It has been a very rewarding experience to document the systems and give ownership of the systems, network and processes back to the owner.

The documentation I have provided included

  • A high level network diagram as a quick reference overview for new administrators and developers
  • An overall application and major network, server and node object description
  • Detailed per server/node description with connection documentation,  critical processes , important paths and files and dependencies
  • Contact Information for the people and companies that the systems rely on.

As a business owner myself,  I have tried to help the client recognize that even when they use an outside consultant,  it is VERY important that they maintain details of their critical business processes INSIDE of their company.  Their might not be anything in business that is as rewarding as giving ownership of a “lost” system back to a client.

 

Matraex Upgraded Mail Client From Squirrelmail to Roundcube

Matraex Upgraded Mail Client From Squirrelmail to Roundcube

Matraex has officially upgraded our web based mail client from Squirrelmail to Roundcube.

Roundcube is a modern mail client utilizing newer technologies for faster and more feature rich mail interaction.  Roundcube runs on our Linux webservers, utilizing Apache, PHP and MySQL.  The software connects to the mail server using the IMAP protocol.

All address book contacts and preferences were imported to Roundcube from Squirellmail at the time of the transition.

As well as updating and implementing their own technologies, Matraex provides server administration, open source production implementation and software customizations to business as a service.

Users with questions about the new mail service or Matraex Consulting Services should contact:

Michael Blood
Matraex, Inc
208.344.1115
www.matraex.com

 

Call Now Button(208) 344-1115

SIGN UP TO
GET OUR 
FREE
 APP BLUEPRINT

Join our email list

and get your free whitepaper