Sunday, November 14, 2010

PHP: Effortless CAPTCHA

Include the following code in your form action reciever:


<?
$verif_box = $_POST["verif_box"];
    if(md5($verif_box).'a4xn' == $_COOKIE['tntcon']){
                      //they got the CAPTCHA correct
                      setcookie('tntcon','');
   }else{
                    //they did not get CAPTCHA correct
   }
?>



Here is a snippet of the part of the form that displays the CAPTCHA:

<tr>

<td valign="top">Type number you see:</td><td valign="top">

<input name="verif_box" type="text" id="verif_box" style="padding:2px; border:1px solid #CCCCCC; width:180px; height:14px;font-family:Verdana, Arial, Helvetica, sans-serif; font-size:11px;"/>

<img src="verificationImage.php?<?php echo rand(0,9999);?>" alt="verification image, type it in the box" width="50" height="24" align="absbottom" /><br />

<!-- if the variable "wrong_code" is sent from previous page then display the error field -->

<?php if(isset($_GET['wrong_code'])){?>

<div style="border:1px solid #990000; background-color:#D70000; color:#FFFFFF; padding:4px; padding-left:6px;width:295px;">Wrong verification code</div><br />

<?php ;}?>

</td><td>

<INPUT TYPE="SUBMIT" NAME="submit" VALUE="Submit">

</td>



Contents of verificationImage.php:



// -----------------------------------------
//  The Web Help .com
// -----------------------------------------
<?
header('Content-type: image/jpeg');

$width = 50;
$height = 24;

$my_image = imagecreatetruecolor($width, $height);

imagefill($my_image, 0, 0, 0xFFFFFF);

// add noise
for ($c = 0; $c < 40; $c++){
    $x = rand(0,$width-1);
    $y = rand(0,$height-1);
    imagesetpixel($my_image, $x, $y, 0x000000);
    }

$x = rand(1,10);
$y = rand(1,10);

$rand_string = rand(1000,9999);
imagestring($my_image, 5, $x, $y, $rand_string, 0x000000);

setcookie('tntcon',(md5($rand_string).'a4xn'));

imagejpeg($my_image);
imagedestroy($my_image);
?>

PHP: The Ultimate E-mail Verification Routine

function validEmail($email)
{
    $isValid = true;
    $atIndex = strrpos($email, "@");
    if (is_bool($atIndex) && !$atIndex)
    {
    $isValid = false;
    }
    else
    {
    $domain = substr($email, $atIndex+1);
    $local = substr($email, 0, $atIndex);
    $localLen = strlen($local);
    $domainLen = strlen($domain);
    if ($localLen < 1 || $localLen > 64)
    {
    // local part length exceeded
    $isValid = false;
    }
    else if ($domainLen < 1 || $domainLen > 255)
    {
    // domain part length exceeded
    $isValid = false;
    }
    else if ($local[0] == '.' || $local[$localLen-1] == '.')
    {
    // local part starts or ends with '.'
    $isValid = false;
    }
    else if (preg_match('/\\.\\./', $local))
    {
    // local part has two consecutive dots
    $isValid = false;
    }
    else if (!preg_match('/^[A-Za-z0-9\\-\\.]+$/', $domain))
    {
    // character not valid in domain part
    $isValid = false;
    }
    else if (preg_match('/\\.\\./', $domain))
    {
    // domain part has two consecutive dots
    $isValid = false;
    }
    else if
    (!preg_match('/^(\\\\.|[A-Za-z0-9!#%&`_=\\/$\'*+?^{}|~.-])+$/',
    str_replace("\\\\","",$local)))
    {
    // character not valid in local part unless
    // local part is quoted
    if (!preg_match('/^"(\\\\"|[^"])+"$/',
    str_replace("\\\\","",$local)))
    {
    $isValid = false;
    }
    }
    if ($isValid && !(checkdnsrr($domain,"MX") || checkdnsrr($domain,"A")))
    {
    // domain not found in DNS
    $isValid = false;
    }
    }
     return $isValid;
}

PHP and MySql Basics: Insert Query

//open conn to db
mysql_connect('dbhostserver','dbusername','password') or die("Failure to connect to db");



mysql_select_db('dbname');

$as_value1 = addslashes($_POST['value1']);
$tr_value1 = trim($as_value1);

$as_value2 = addslashes($_POST['value2']);
$tr_value2 = trim($as_value2);

$query = "INSERT INTO table(value1, value2) VALUES ('$tr_value1', '$tr_value2')";
$result = mysql_query($query);
if(mysql_affected_rows()==1){
   $message = "Successfully added to db.";
}else{
   $message = "Could not add to db.";
}

PHP Basics: Connect to MySql and Query

//open conn to db
mysql_connect('dbhostserver','dbusername','password') or die("Failure to connect to db");

mysql_select_db('dbname');

$query = "SELECT stuff FROM tablename";
$result = mysql_query($query);
while($name_row = mysql_fetch_row($result)){
    print("$name_row[0]<BR>\n");
}

Thursday, November 11, 2010

Transact SQL (T-SQL) Basics

DECLARE and SET Varibales
DECLARE @Mojo int
SET @Mojo = 1
SELECT @Mojo = Column FROM Table WHERE id=1

IF / ELSE IF / ELSE Statement
IF @Mojo < 1
BEGIN
'do something
END
ELSE IF @Mojo = 1
BEGIN
'do something
END
ELSE
BEGIN
'do something
END

CASE Statement
SELECT Day = CASE
WHEN (DateAdded IS NULL) THEN 'Unknown'
WHEN (DateDiff(day, DateAdded, getdate()) = 0) THEN 'Today'
WHEN (DateDiff(day, DateAdded, getdate()) = 1) THEN 'Yesterday'
WHEN (DateDiff(day, DateAdded, getdate()) = -1) THEN 'Tomorrow'
ELSE DATENAME(dw , DateAdded)
END
FROM Table

SQL: Finding DUPLICATES

For some odd reason, I have it stuck in my mind that you need to use JOINS to find duplicates (maybe it was something that Access made you do way back in the day). I know there is SOMETHING out there that seems like it would be very easy to do in SQL but in fact involves duplicating the table in an alias. But finding duplicates isn't it.

SELECT username,
COUNT(username) AS NumberOfOccurences
FROM users
GROUP BY username
HAVING ( COUNT(username) > 1 )