PHP MySQL

mike719 Level 1

March 1, 2013, 12:28 pm

Write the reason you're deleting this FAQ

PHP MySQL

Still using deprecated PHP functions because I haven't the time to learn mysqli or PDO stuff. Any suggestions for good tutorials or which one is better?

Perhaps someone can make the case for me to finally learn and switch my sites over to it.

Comments

Please login or sign up to leave a comment

Join

Promodrone

Perhaps someone can make the case for me to finally learn and switch my sites over to it.

@mike719, 1st: a graphic representation of why you should learn how to protect your database-driven sites with PHP Data Objects;

Two exploits that most database-driven scripts with user input will be prone to suffer are "SQL Injection" and SQL "Blind" Injection. The FIRST lesson that one is imparted in a decent-to-good programming tutorial is the fact that user input is to NEVER be trusted. In the case of SQL Blind Injection, an attacker isn't getting any error messages tipping them off to the possible server configuration (or misconfiguration). But the effects of both types of attack can result in the rightful database owner being locked out of their table structures, with the attacker pawning all the access/manipulation privileges. The main fear is that the attacker decides to vandalize the database entirely by just "dropping" it - the process of deleting all table data and structures with a single click. Devastating, and I've experienced it once this lifetime, so I know how clowned one feels after an episode such as this.

The main exploit point comes when using "dynamic URLs" [domainname.com?id=255], where a database ID is appended to the URL. With the ID representing fixed database information, the main MySQL query to select that specific information would be, [SELECT * FROM tableName WHERE ID = '255';] - to which an attacker may try an SQL query injection in the form of [domainname.com?id=255 AND 1=1]. The attacker is counting on a possible server misconfiguration to allow the characters after the ID to be accepted and allowed to run. But, that's a BIG "if", with most Server Admins steadfastly preoccupied with all manner of Security. Once the attacker has verified the condition for the exploit vulnerability actually exists, they can use more sophisticated methods to secure server root level access.

Where the beauty of PDO kicks in is in the fact that it uses prepared statements (parameterized queries) to pass in the data from the user input form so that the mixing of the code and the data aren't allowed until everything is at the database level. @mike719, I'm trying to keep it as simple as possible (as I understand it), and I'll be the first to say that there is SO much I have yet to still read and understand on the subject even though I've the following type of code to (successfully) submit data from a form to a database;

<? PHP
try {
$dsn = 'mysql:host=localhost;dbname=dbname';
$username ='root';
$password ='YerPWD';
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES, false); 
$dbh = new PDO($dsn, $username, $password, $options);
} catch (Exception $e) {
  echo 'WTF?!: ' . $e->getMessage();
}   
    $id = $_POST['id'];
    $title = strip_tags($_POST['title'], ''); 
    $url = strip_tags($_POST['url'], 'http://'); 
    $body = $_POST['body'];
    $category = $_POST['category'];

// If the fields are not empty, insert it.
        if ($_POST['title'] == null OR trim($_POST['title']) ==''|| $_POST['url'] == null OR trim($_POST['url']) =='' || $_POST['body'] == null OR trim($_POST['body']) =='' || $_POST['category'] == null OR trim($_POST['category']) =='') {
            echo '<br><br><div align="center"><a href="javascript: history.go(-1)"><font color="#0000CC" face="arial black">Please return to form and supply required data!</font></a><br><img src="domainname.com/images/logos.gif"></div>';
            exit;
        } else {
 //Update
try {
$sql = "UPDATE news 
        SET title=?, url=?, body=?, category=?
                WHERE id=?";
$q = $dbh->prepare($sql);
$q->execute(array($title,$url,$body,$category,$id));
                //If successful redirect to display database update details
                if($sql){
        echo '<div align="center"><center><font color="#0000CC" face="arial black" size="5"><br><em>';
        echo $title;
        echo '</em><br><em>';
        echo $url;
        echo '</em><br><em>';
        echo $body;
        echo '</em><em>';
        echo $category;
        echo '</em><br>';
        echo '<font face="arial black" size="5" color="#0000CC">Your Article/News URL Has Been Edited - Thank You!</font><br>
              <a target="_blank" href="news.php"><font face="arial black" size="5" color="#0000CD"><u>Return To News</u></font></a><br>
              <a target="_blank" href="add.php"><font face="arial black" size="5" color="#0000CD"><u>Submit Fresh News</u></font></a><br>
              <form action="nvue.php" target="_self"><input type="Submit" value="Article List"></form>';
        echo '</font></center></div><br><br>';
                }                 
                else{
                echo '<a href="javascript: history.go(-1)"><font color="#0000CC" face="arial black">No record added. Return to form, please!</font></a>';
              }
          } catch (PDOException $e) {
        echo "<b>SQL Error #" . $e->getCode() . "</b> in <b>" . $e->getFile() . "</b> on line <b>" . $e->getLine() . ":</b> " . $e->getMessage() . "\n";
}
          
       }
? >
 

I'm not even trying to claim decent proficiency, but I had to read and read and read, and hack and hack before I got a working semblance of what I posted above. I'm going to point you to a GREAT source for a beginner - phpro.org, as well as give you the search engine query that'll be your friend, "pdo tutorial for beginners". I wish you luck on your journey, @mike719 - you're going to need it...but you're also going to need to do it. ESPECIALLY with all the familiar MySQL functions in a deprecated state and with PDO a STANDARD class within PHP5 (and 6 to come). I hope this at least shed a little light for you. But don't take my word for it - RESEARCH...

[left][font=arial black][color=blue] [quote]Perhaps someone can make the case for me to finally learn and switch my sites over to it.[/quote] @[USER=26011]mike719[/USER], 1st: a graphic representation of why you should learn how to protect your database-driven sites with PHP Data Objects; [url=http://imgs.xkcd.com/comics/exploits_of_a_mom.png][img]http://imgs.xkcd.com/comics/exploits_of_a_mom.png[/img][/url] Two exploits that most database-driven scripts with user input will be prone to suffer are "SQL Injection" and SQL "Blind" Injection. The FIRST lesson that one is imparted in a decent-to-good programming tutorial is the fact that user input is to NEVER be trusted. In the case of SQL Blind Injection, an attacker isn't getting any error messages tipping them off to the possible server configuration (or misconfiguration). But the effects of both types of attack can result in the rightful database owner being locked out of their table structures, with the attacker pawning all the access/manipulation privileges. The main fear is that the attacker decides to vandalize the database entirely by just "dropping" it - the process of deleting all table data and structures with a single click. Devastating, and I've experienced it once this lifetime, so I know how clowned one feels after an episode such as this. The main exploit point comes when using "dynamic URLs" [domainname.com?id=255], where a database ID is appended to the URL. With the ID representing fixed database information, the main MySQL query to select that specific information would be, [SELECT * FROM tableName WHERE ID = '255';] - to which an attacker may try an SQL query injection in the form of [domainname.com?id=255 AND 1=1]. The attacker is counting on a possible server misconfiguration to allow the characters after the ID to be accepted and allowed to run. But, that's a BIG "if", with most Server Admins steadfastly preoccupied with all manner of Security. Once the attacker has verified the condition for the exploit vulnerability actually exists, they can use more sophisticated methods to secure server root level access. Where the beauty of PDO kicks in is in the fact that it uses prepared statements (parameterized queries) to pass in the data from the user input form so that the mixing of the code and the data aren't allowed until everything is at the database level. @[USER=26011]mike719[/USER], I'm trying to keep it as simple as possible (as I understand it), and I'll be the first to say that there is SO much I have yet to still read and understand on the subject even though I've the following type of code to (successfully) submit data from a form to a database; [code] 'SET NAMES utf8', PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES, false); $dbh = new PDO($dsn, $username, $password, $options); } catch (Exception $e) { echo 'WTF?!: ' . $e->getMessage(); } $id = $_POST['id']; $title = strip_tags($_POST['title'], ''); $url = strip_tags($_POST['url'], 'http://'); $body = $_POST['body']; $category = $_POST['category']; // If the fields are not empty, insert it. if ($_POST['title'] == null OR trim($_POST['title']) ==''|| $_POST['url'] == null OR trim($_POST['url']) =='' || $_POST['body'] == null OR trim($_POST['body']) =='' || $_POST['category'] == null OR trim($_POST['category']) =='') { echo '

Please return to form and supply required data!

'; exit; } else { //Update try { $sql = "UPDATE news SET title=?, url=?, body=?, category=? WHERE id=?"; $q = $dbh->prepare($sql); $q->execute(array($title,$url,$body,$category,$id)); //If successful redirect to display database update details if($sql){ echo '

'; echo $title; echo '
'; echo $url; echo '
'; echo $body; echo ''; echo $category; echo '
'; echo 'Your Article/News URL Has Been Edited - Thank You!
Return To News
Submit Fresh News
'; echo '

'; } else{ echo 'No record added. Return to form, please!'; } } catch (PDOException $e) { echo "SQL Error #" . $e->getCode() . " in " . $e->getFile() . " on line " . $e->getLine() . ": " . $e->getMessage() . "\n"; } } > [/code] I'm not even trying to claim decent proficiency, but I had to read and read and read, and hack and hack before I got a working semblance of what I posted above. I'm going to point you to a GREAT source for a beginner - phpro.org, as well as give you the search engine query that'll be your friend, "pdo tutorial for beginners". I wish you luck on your journey, @[USER=26011]mike719[/USER] - you're going to need it...but you're also going to need to do it. ESPECIALLY with all the familiar MySQL functions in a deprecated state and with PDO a STANDARD class within PHP5 (and 6 to come). I hope this at least shed a little light for you. But don't take my word for it - RESEARCH... [/color][/font][/left]

10 years ago

Are you sure you want to delete this post?

CodeClerks

PHP MySQL

PHP MySQL

Comments

Please login or sign up to leave a comment