WEB Any tips for a SQL noob so i wont get owned / sql injected

Discussion in 'OT Technology' started by Ricky, Dec 15, 2008.

  1. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    I have a pretty simple code.

    Im guessing it can be exploited easily so any tips to fix any potential holes will be greatly appreciated.

    Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
       "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 
    <html xmlns="http://www.w3.org/1999/xhtml"> 
    <head> 
    <title>Songs</title> 
    <meta http-equiv="content-type" 
       content="text/html; charset=iso-8859-1" /> 
    </head> 
    <body> 
    
    <?php if (isset($_GET['addsong'])): // User wants to add a song 
    ?> 
    
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> 
    <label>Type your song here:<br /> </label>
       <p>artists <textarea name="artists" rows="1" cols="40"> </textarea> </p>
    <p> title 	<textarea name="title" rows="1" cols="40"> </textarea> </p>
    <p> bpm	<textarea name="bpm" rows="1" cols="40"> </textarea> </p>
    	
    <br /> 
    <input type="submit" value="SUBMIT" /> 
    </form> 
    
    <?php else: // Default page display 
    
     // Connect to the database server 
     $dbcnx = @mysql_connect('localhost', 'USERNAME', 'PASSWORD'); 
     if (!$dbcnx) { 
       exit('<p>Unable to connect to the ' . 
           'database server at this time.</p>'); 
     } 
    
     // Select the song database 
     if ([email protected]_select_db('RICKY_DATABASE')) { 
       exit('<p>Unable to locate the song ' . 
           'database at this time.</p>'); 
     } 
    
     // If a song has been submitted, 
     // add it to the database. 
    if (isset($_POST['title'])) {
       $artists = $_POST['artists']; 
       $title = $_POST['title']; 
       $bpm = $_POST['bpm']; 
    
       $sql = "INSERT INTO songs SET 
           artists='$artists', 
           title='$title', 
           thedate=CURDATE(),
           bpm='$bpm'"; 
       if (@mysql_query($sql)) { 
         echo '<p>Your song has been added.</p>'; 
       } else { 
         echo '<p>Error adding submitted song: ' . 
             mysql_error() . '</p>'; 
       } 
     } 
    
     echo '<p>Here are all the songs in our database:</p>'; 
    
     // Request the text of all the songs 
     $result = @mysql_query("SELECT `id`,`artists`,`title`,`bpm` FROM `songs`  ORDER BY `thedate` DESC LIMIT 0, 10"); 
     if (!$result) { 
       exit('<p>Error performing query: ' . 
           mysql_error() . '</p>'); 
     } 
    
    //display song
    
    // Displays alternate table row colors
    function row_color($i){
    $bg1 = "#0099FF"; // color one 
    $bg2 = "#336699"; // color two
    
    if ( $i%2 ) {
    return $bg1;
    } else {
    return $bg2;
    }
    }
    
    
    //start table
    echo "<table bgcolor=#FFFFFF border=0 cellpadding=1 cellspacing=1><tr>
    		<td>Artist</td>
    		<td>Song Title</td>
    		<td>BPM</td>
    	</tr>\n";
    
    //content table
    for( $i = 0; $i < $row = mysql_fetch_array($result); $i++){
    echo "<TR>\n"
    ."<TD bgcolor=".row_color($i).">".$row["artists"]."</TD>\n"
    ."<TD bgcolor=".row_color($i).">".$row["title"]."</TD>\n"
    ."<TD bgcolor=".row_color($i).">".$row["bpm"]."</TD>\n" 
    ."</TR>";
    }
    echo "</TABLE>";
    
    
     // load add page
     echo '<p><a href="' . $_SERVER['PHP_SELF'] . 
         '?addsong=1">Add a song!</a></p>'; 
    
    endif; 
    ?> 
    </body> 
    </html>
    Any other general tips are appreciated too. Im real new to mysql so dont flame me :o
     
  2. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    Also if you dont mind pointing me in the right direction in getting a search form working.

    I know i have to use the count function to check for any results and the select function too. Im just a little confused on how to actually code it :o

    edit: i managed to get a little search thing going with the help of google but it still seems its only searching 1 of the fields instead of using my OR condition and searching both.

    i also tried this

    and

    and it still doesnt work. Only works when you search one field at a time.

    any idea?
     
    Last edited: Dec 15, 2008
  3. HYBR|D

    HYBR|D Beep Beeeeee'p

    Joined:
    May 5, 2006
    Messages:
    2,009
    Likes Received:
    0
    Location:
    Ozz
  4. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    fucking sweet.

    thanks broseph.
     
  5. HYBR|D

    HYBR|D Beep Beeeeee'p

    Joined:
    May 5, 2006
    Messages:
    2,009
    Likes Received:
    0
    Location:
    Ozz
    No guarantees it will work or help, but it's worth a shot.. :)
     
  6. 07

    07 18-1

    Joined:
    Jun 26, 2006
    Messages:
    4,933
    Likes Received:
    0
    I know you just need to not allow characters such as %, /, <, >, etc..... Disallow anything that is not a letter.

    Mind you, I basically know nothing with programming, but do know to block all that junk from being inputted.
     
  7. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    Have a read of this: SQL Injection from PHP Manual

    For MySQL you can use the mysql_real_escape_string to escape the values before you put them in a SQL statement (and prevent a SQL injection attack).

    Alternatively, you can use the Improved MySQL Extension: http://www.php.net/manual/en/book.mysqli.php and its objects or functions (such as prepared statements, and mysqli_real_escape_string etc)

    If you're not comfortable with OO, you can stick with the functions instead (and they're just about identical, except they start with mysqli_ instead of mysql_).

    Also it might pay to get familiar with PDO which is a database abstraction layer.

    You should also call htmlentities on the user-submitted data to prevent them from doing XSS attacks and similar when you're actually outputting it into the page e.g. $blah = htmlentities($_GET['foo']);

    If you want to learn more, PHP Security Guide is interesting and covers most of the security concerns.
     
  8. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    For your query, you can't double-up the statements with the OR, you have to repeat yourself:

    $search = mysql_real_escape_string($search);
    $result = mysql_query("SELECT * FROM songs WHERE artists LIKE '%$search%' OR title LIKE '%$search%'");
     
  9. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    So like with the real escape string, will it be something like this ?

    and also
    thanks i never realized you cant double it up like that :o
     
  10. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    or will this work?

     
  11. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    Code:
    $search= mysql_real_escape_string(htmlentities($_POST["search"]));
    
    $result = mysql_query("SELECT * FROM songs WHERE title LIKE '%$search%' OR artists LIKE '%$search%'");
    
    while($row= mysql_fetch_array($result))
    {
        $title = $result['title'];
        etc
    }
    
    You don't have to escape stuff coming out of the database, only inserting it.

    So inserting the data:

    $title = mysql_real_escape_string(htmlentities($_POST['title']));
    mysql_query( "INSERT INTO blah (title) VALUES ('$title')" );

    Pulling it out:

    $title = $result['title'];
     
  12. Ender0910

    Ender0910 woot!

    Joined:
    Jun 1, 2004
    Messages:
    3,039
    Likes Received:
    0
    Location:
    Redmond/Bay Area
    Like whup said, you don't need to sql_escape anything coming from the database. If you want to be thurough, you may need to make sure XSS cannot happen from user inserted data.

    You should sql_escape any query you execute, though, not only inserts.

    Also, I don't think this applies to this example, but another type of protection you could use is checking the input type: http://us.php.net/manual-lookup.php?pattern=is_
    or using Stored Procedures
     
  13. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    Okay i got the search part completed but now the actual adding data part is fucking with me :o

    it has the ifset

    i know this doesnt work
     
  14. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
  15. Phasm

    Phasm OT Supporter

    Joined:
    Sep 20, 2005
    Messages:
    9,680
    Likes Received:
    0
    Location:
    Michigan
    just dont drop da soap
     
  16. Javi

    Javi New Member

    Joined:
    Sep 13, 2004
    Messages:
    37,785
    Likes Received:
    0
    Location:
    Houston, TX
    in asp.net land:

    use IsNumeric to verify numbers
    for strings - create a function to add the '' and look at the string before creating the sql query.
     
  17. Javi

    Javi New Member

    Joined:
    Sep 13, 2004
    Messages:
    37,785
    Likes Received:
    0
    Location:
    Houston, TX
    oh yeah be sure to back up your website as often as you can. One of the sites I do maintenance on was SQL injected. Luckily I had a backup from 9 hours earlier :noes:
     
  18. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    you should sanitize that array so you don't get pwnt. blindly mapping shit = :nono:
     
  19. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    isset only checks variables; you will get errors if you try passing it anything else (such as function results like you're trying). It's a safe way of seeing if a variable or value has been set without generating a notice or warning from PHP.

    I often use empty() instead of isset(), because it not only checks if a variable has not been set, but if it's also empty. You just have to be careful because a variable set to 0 will evaluate as empty!

    e.g.

    if( empty($_POST['title'] ) $error = "Please enter a title!";

    You're doing ok, but I encourage you spend a bit of extra time selecting and learning a framework that will handle a lot of these things for you such as forms, security, presentation.

    I use my own frameworks so i can't recommend one from experience but Zend Framework, Code Igniter, Symfony etc all look like good options.
     
  20. Ricky

    Ricky █▄ █▄█ █▄ ▀█▄

    Joined:
    Jun 17, 2005
    Messages:
    38,767
    Likes Received:
    6
    thanks :o

    i have to read this thread over. then read it again :o

    i guess this shit is a little complicated at first since im trying to learn both mysql and php at the same time. :sad2:
     
  21. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    Another thing I like to do that hasn't been mentioned is trim the inputs. For example, say you have an input in an address form for "State", I'd trim it down to 25 characters before putting it into an SQL query.

    This needs to be done with php/asp btw, max length fields on html forms are easy to get around.

    My asp function I run on all Inputs looks like this

    cleanUserInput(input, maxStringLength)
     
  22. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC
    Limiting the length on the field doesn't really do anything for you though. I'm talking about limiting the length of the string bf it's inserted into the query.

    Here's an example of what I mean
    Say someone enters this string into the login_id field of a login form
    x'; INSERT INTO members ('email','passwd',login_id,'full_name') VALUES ('[email protected]','hello','steve69','Steve thenewAdmin');--

    Sql query built would be something like
    Code:
    SELECT email, passwd, login_id, full_name FROM users 
     WHERE login_id = 'x'; INSERT INTO members ('email','passwd',login_id,'full_name') 
    VALUES ([email protected]','hello','steve69','Steve thenewAdmin');--';
    trimming the length on login_id variable to say 30 characters would make the above query look like this below

    Code:
    SELECT email, passwd, login_id, full_name FROM users 
     WHERE login_id = 'x'; INSERT INTO members ('emai
     
  23. 95vr4

    95vr4 OT Supporter

    Joined:
    Oct 6, 2004
    Messages:
    2,513
    Likes Received:
    0
    Location:
    Weddington, NC

Share This Page