PHP, Oracle and encodings

July 22nd, 2010

As I had previously posted I’m involved in several PHP-Oracle projects.

One involves ecommerce.

We’re doing this ecommerce for Latin America so it should handle special characters such as accents, ñ and others. Web page encoding is iso8859-1.

Every time a user made an insert, the database was recording a ‘?’ character instead of accents or other latin chars.

We tested for utf8_encode and utf8_decode functions, they were useless so we figured it out that the database was storing info in some other encoding. We added apache conf and meta headers to view the page as utf8 but again garbagge was being stored.

We were really angry so we started googling for some answers.

First of all we needed to know what encoding was the database using. We found out with the following query:

select * from NLS_DATABASE_PARAMETERS;

We got this from here.

After that we understood that we needed to enable the proper encoding for Oracle and PHP, how did we do it?

After a while we ended here.

So the solution was very simple. We added the following line to the main conf file:

putenv(“NLS_LANG=AMERICAN_AMERICA.AL32UTF8”);

After that we added utf8_encode and utf8_decode to the code and voilá! everything is working fine!

It was indeed a very exhausting work to figure this out so I’m posting it here to have a quick access to the solution.

PHP & Oracle

July 21st, 2010

Long time no see.

Changed job and been working on fixing an app.

In my new job PHP and Oracle are the standards so I’m back to Oracle and I already had my first issues.

I have a small database with a couple of related tables.

I wanted to do an insert and get the id of the recently added row, searched for it on Google and there it was, I’ll need to use RETURNING…INTO.

It’s pretty simple actually so I coded it, the primary key is an integer that runs a trigger with a sequence, kind of a MySQL autoincrement field.

So this is how the code ended looking:

$query = “SELECT id, field1, field2 FROM table WHERE field1 IS NOT NULL RETURNING id into :id”;

$con = new connection()

$stmt = oci_parse($con,$query);

if($stmt!==false){

$binded = oci_bind_by_name($stmt,’:id’,$id);

$executed = oci_execute($stmt);

if($executed!==false){

$row = oci_fetch_array($stmt,OCI_BOTH+OCI_RETURN_NULLS+OCI_RETURN_LOBS);

}

}

It worked fine for a couple of days, then id started working oddly. I analized the code and found out that id was returning a 3 digits id instead of 4 digits, so I’ll be getting 123 for ids 1230 – 1239 besides the previous original 123. That was messing my database integrity and making my code to fail.

Googled it and found nothing! What the hell was wrong with it?

Finally after a day I calmed down and started to remember the old times when I started to work with Oracle and suddenly I remembered!!!!

I was missing a parameter on an oci function. I added it and voila! It worked!

What was I missing? Simple, I was missing the maxLength parameter for the oci_bind_by_name function.

Finally it ended looking like this:

$binded = oci_bind_by_name($stmt,’:id’,$id,100);

Hope this helps others with the same issue.

Weird characters instead of latin!!

April 20th, 2010

Well I’m moving from a home server to a virtual dedicated server.

It’s been hours of configurations and moving databases, files and stuff but I’m almost done. Almost all my test sites are up and running. A couple of minutes ago I uploaded a site that uses a lot of latin characters like ñ and accents. I wasn’t worried because I had my meta tags ‘properly’ set.

To my surprise once the site was up and I opened the URL it was showing weird characters instead of the latin characters!!!

Why?! My meta tags are correct! What could be wrong?

So I went back and started reviewing the web server conf file… I remembered that there was a conf variable I had to set for my metas to work… finally I got to this lines:

# Specify a default charset for all content served; this enables
# interpretation of all content as UTF-8 by default.  To use the
# default browser choice (ISO-8859-1), or to allow the META tags
# in HTML content to override this choice, comment out this
# directive:
#
AddDefaultCharset UTF-8
Ok so this is the conf variable I was searching. As always I didn’t read the commented words and put ISO-8859-1 instead of UTF-8. Some sites were OK but others went ahead with the weird characters!!!
I went back to editing the conf file but this time I took the time to read the comment. BINGO!
If this variable is set then the server will overwrite the meta define value of my web page which in this case was:
meta http-equiv=”Content-Typecontent=”text/html; charset=ISO-8859-1
So what I did was comment that line out and now my web server does check for the proper meta.
Great!!!!

Setting up https with Apache

April 14th, 2010

Been months since I wanted to do this.

I always thought that setting up https with Apache was very complicated. Specially because of the certificates.

For the last couple of days I’ve been working on setting up a linux server. It would be used for hosting a couple of pages so I need it to have the basic functionality like smtp, pop, imap, http and ssh as well as a couple of other things like memcached.

I had set up httpd before but honestly never https because I thought it was very difficult but it isn’t!!!

Things are really really simple, follow these simple steps once you have httpd running:

1.Install mod_ssl for apache

yum install mod_ssl

2.Restart httpd

service httpd restart

Now you have https available with a default certificate! so the next step would be creating your own certificate

3. Run the following command

openssl req $@ -new -x509 -days 365 -nodes -out /var/www/html/certs/mycert.pem -keyout /var/www/html/certs/mycert.pem

Remeber to change the path to your certificate or copy it later to the httpd certificate’s path.

You’ll be asked for some info.

4. Open ssl.conf (In my case, I have a Centos 5 distro, it’s the following path: /etc/httpd/conf.d/ssl.conf) and search for the following line:

SSLCertificateFile /etc/pki/tls/certs/localhost.crt

Replace the path with your own certificate’s path.

Comment the following line:

SSLCertificateKeyFile /etc/pki/tls/private/localhost.key

5.Now restart httpd

Done! you have https running on port 443 with your own certificate.

Remember the certificate is valid for your site but since it’s not registered with an authority browsers will keep sending a warning.

Sanitizing and validating data

March 25th, 2010

Still working on a project I found the typical login form.

This time the login credentials are the user’s email and a password. As always I found myself facing the task of validating an email. I had some php functions to do so but since they were almost a year old I decided to search for a more powerful already working solution.

To my surprise I found some very interesting info about email’s validation that I was unaware.

Some stuff that caught my eyes was that the email RFC actually allows the use of special characters on an email. The followin email is valid:

“jimmy”\@home@gmail.com

Noticed the quoted string? what about the escaped @?

In fact the following characters are valid on an email username: !#$%&’*+-/=?^_`{|}~@.[]

Wow! I bet that most of us didn’t knew that, and what’s worst most javascript or PHP validation functions won’t validate with the proper RFC rules. Deep trouble!

The good news are that PHP as of version 5 provides some functions that would help us deal with this problem.

Read the manual and search for filters, you’ll find a couple of very interesting and helpful functions to sanitize and validate data.

As for the email the following gets the job done:

$sanitized_email = filter_var($email,FILTER_SANITIZE_EMAIL);

$valid_email = filter_var($sanitized_email,FILTER_VALIDATE_EMAIL);

if($valid_email!==false){

echo “Valid email!”;

}

It’s indeed a very helpful and concise way of validating data. The only bad thing is that it only works on PHP 5 or later. Nevertheless you’ll find some very powerful scripts on the links previously provided.

Hope this helps you as much as it helped me.

When random ain’t that random

March 24th, 2010

Been working on a small project that demanded the use of captcha.

Since it’s very small I decided to create my own captcha class. Nothing very complicated but still enough to avoid most of the spam robots. Anyway In order to create a nice captcha mi code draws some lines whose x,y coordinates where randomly generated through PHP’s rand function.

Later that day as I was finishing a widget I came up with this cool site. It’s all about randomness so I started reading.

To my surprise I found out a specific page that talked about pseudo random numbers generators (PRNG) and true random numbers generators (TRNG). PHP’s rand function is a PRNG and it’s not as cool as I thought.

For what the page explains the rand function should be avoided for true random number generatiosn when on a Windows server. Apparently it behaves oddly and follows some sort of pattern. They recommend the use of mt_rand instead which generates a more random number and also is faster than rand!!!!

Obviously I moved to mt_rand.

Please read the article, you’ll be surprised!!!

SocialGo API

February 18th, 2010

We’ll this post is for remembering one very important issue with the SocialGo API.

I was doing a webpage that send users to their SGo page. So I used the SGo API.

I downloaded it and realized that when you download the API the corresponding keys (developer and network) are already assigned to the corresponding properties so I was doing this:

$api = new SNM_api();

$id = 'test@example.com'; //. user email OR user ID
$result = $api->getUserCrossDomainAuthURL($id);
$redirect_user_url = $result->member->attributes()->auth_url;

I’ve been dealing with the fact that I kept getting false as a result… What’s wrong with the code?

So I went to the blogs, even the SocialGo blogs and some developers wrote me saying I needed to set the network and developer keys. I was reluctant because the object already had the pproperties set so I thought it was a silly answer, till minutes ago when I finally gave up trying other solutions. It worked! I haven’t takn a look to the SGo API but it looks that you need to set this properties in order for the API to work so the code ends up like this:

$api = new SNM_api();

$api->setApiServer('http://api.socialgo.com/');
$api->setNetworkKey('<NETWORK_KEY>');
$api->setDeveloperKey('<DEV_KEY>');

$id = 'test@example.com'; //. user email OR user ID
$result = $api->getUserCrossDomainAuthURL($id);
$redirect_user_url = $result->member->attributes()->auth_url;
I'll never forget about this again.

call to undefined function json_decode

February 14th, 2010

This sucks!

Again my dev env works perfectly and the production environment keeps throwing weird messages.

I’m using php json_decode to (you guessed it) decode  a json string. The server throws this ugly message!

What should I do?! Ok I realized my dev env uses PHP 5.2…. the production server uses 5.1.6. What does that have to do? Well for some reason PHP 5.1.6 doesn’t have json support, in order to support the calls you should either upgrade php or pear install it.

I tried updating to php 5.2, and didn’t worked so I ended installing the pear support.

Remember to include the proper paths.

Lock wait timeout exceeded; try restarting transaction

February 14th, 2010

So last friday I came up with this nasty message… Believe me, it was a very troublesome issue to solve. We have MySQL running Innodb for a couple of tables. We wanted to modify 3 tables and we decided to create a transaction if inserts and updates went ok then commit if not rollback.

I did it and tested it on my dev env (my lap hehehehe) and it all worked fine, speed was ok and everything worked as wanted. So I commited it to my svn server and uploaded the code to the production server. 5 min. before I leave the office my boss decided to test the new version…. good and bad idea, good because he got the nasty error, bad because of the time.

Anyway, we saw the error. Initially I thought it was because of a web service (no, I didn’t realized it was a database issue), then after discarding lots of probable errors I realize it was a transaction error. After hours looking and reading google search results I learned the following: When doing a transaction tables involved receive a lock, the lock is released once the transaction ends. If a table is being updated within the transaction and you try to select data from it then you’ll be trying to access a locked table, since it is locked you can’t read from it and since you can’t read from it you can’t finish the transaction so the waiting for the lock to release takes for ever and the connection throws the error:

“Lock wait timeout exceeded; try restarting the transaction”

How did I solved it?

After trying docens of solutions without success I finally realized that I had to take the reading statements off the transaction.

It worked! And because I forget things very fast I decided to put it here.

Hope it helps someone else out there.

Sendmail issues

January 14th, 2010

Been a while.

I bought the PS3 and a game called Battlefield: Bad Company…. it’s way too addictive… By the way my PSN nick is andreakis so if you wish to add me ur welcome hehehe

Today I was installing a system that sends emails. The server has sendmail installed and mails kept going into the queue and were never sent… The queue report said the following:

“Deferred: Connection refused by XXXXXXXXXXXX”

So it looked like mails were not being sent. I Googled for the solution and finally one worked. It’s very simple, just change the following line:

O DaemonPortOptions=Port=smtp,Address=127.0.0.1, Name=MTA

For this:

O DaemonPortOptions=Port=smtp, Name=MTA

It worked for me, still don’t know why but I’ll digg deep into it this weekend.

Hope this helps!