www.webdeveloper.com
Results 1 to 14 of 14

Thread: mysql quotations

  1. #1
    Join Date
    Dec 2010
    Posts
    7

    mysql quotations

    I am trying to put height into mysql database table through an entry made in php. All other fields, name, age, etc. work fine, but when it comes to putting height in the foot-inch format (5'6") there is a problem.
    How do I make it so the characters ' and " can be inserted into mysql table?
    If it helps, this is the message I get when the form is submitted: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6"')' at line 2

  2. #2
    Join Date
    Aug 2009
    Posts
    593
    Using mysql_real_escape_string should sort the problem out...

    Eg.

    PHP Code:
    $query 'INSERT INTO table (name, height) VALUES ("' mysql_real_escape_string($name$db_conn) . '", "' mysql_real_escape_string($height$db_conn) . '")'

  3. #3
    Join Date
    Mar 2010
    Posts
    672
    Mysql_real_escape_string will fix that issue as already mentioned. But this also means that you're not properly sanitizing your input variables. Just remember to always run any input through a sanitizing function like mysql_real_escape_string(), or else you will make yourself vulnerable to sql injection attacks.
    And for future reference, if any character may be interpreted as anything besides data in mysql you can also manually escape that character using a \ and it will be ignored.

  4. #4
    Join Date
    Dec 2010
    Posts
    7
    Great, thanks. I thought I had to use mysql_real_escape_string, but I wasn't sure how.
    Thankfully this is all for test sites so I can become familiar with php/html/java before doing anything final, so filtering is not my TOP priority. Just as long as it works, for now. I have no hosts yet, just using localhost.
    One more thing, what is
    PHP Code:
    $db_conn 
    ? It all worked, but what does:
    mysql_real_escape_string() expects parameter 2 to be resource, null given in
    mean?

  5. #5
    Join Date
    Aug 2009
    Posts
    593
    $db_conn is my pretend database connection for the example... it is the resource passed back to you when you invoked mysql_connect initially.

    Eg.

    PHP Code:
    $db_conn mysql_connect($server$username$password); 

  6. #6
    Join Date
    Mar 2010
    Posts
    672
    Quote Originally Posted by SamE4u View Post
    Great, thanks. I thought I had to use mysql_real_escape_string, but I wasn't sure how.
    Thankfully this is all for test sites so I can become familiar with php/html/java before doing anything final, so filtering is not my TOP priority. Just as long as it works, for now. I have no hosts yet, just using localhost.
    One more thing, what is
    PHP Code:
    $db_conn 
    ? It all worked, but what does:
    mysql_real_escape_string() expects parameter 2 to be resource, null given in
    mean?
    $dn_conn is the handle used for the database connection, and your error message is related to the fact that $db_conn is not the name of the handle you used for the database connection, hence its null.
    However, i don't believe you have to specify the connection handle unless you have multiple database connections going on at once as it will automatically use the last database connection made.

  7. #7
    Join Date
    Dec 2010
    Posts
    7
    Hm. its still not working. I'll give you the code. When I submit 5'6" for example, it only shows the 5. Also, whenever I refresh the page, 0's appear across the board as if that is what I submitted.
    PHP Code:
    <?php
    //Takes the post from insert.php and sends it to mysql
     
    $Name $_POST['Name'];
     
    $Age = (int) $_POST['Age'];
     
    $Height $_POST['Height'];
    //connects to mysql with "server", "user", "password"
      
    mysql_connect ("localhost""root""")
       or die(
    mysql_error());
    //select the database "sam"
      
    mysql_select_db ("sam");
    //sends a query to mysql with the "command"
     
    $query="insert into text (Name, Age, Height) values 
     ('" 
    mysql_real_escape_string($Namemysql_connect ("localhost""root""")) ."', 
     '" 
    mysql_real_escape_string($Agemysql_connect ("localhost""root""")) ."', 
      '" 
    mysql_real_escape_string($Heightmysql_connect ("localhost""root""")) ."')
      "
    ;
    //tells mysql to do the query
     
    mysql_query($query)
      or die (
    mysql_error());
     echo 
    "Updated to: " .$Name" , " .$Age" , " .$Height ;
    ?>    

    <?php
    // Connects to your Database from mysql

    // Collects data from "text" table
    // Add a 'where' clause to only show a certain number of ppl on the table
     
    $data mysql_query("select * from text")
      or die(
    mysql_error());
    // Make the cells look good
     
    Print "<table border=2 cellpadding=3 cellspacing=2 width=20%>";
    /* Puts the "text" info into the $info array
    "while" makes the retrieve command a loop so it collects all data*/
    // Print out the contents of the entry
     
    {
     Print 
    "<tr>";
     Print 
    "<th>Name:</th>";
     Print 
    "<th>Age:</th>";
     Print 
    "<th>Height:</th>";
     Print 
    "</tr>";
     }
     while(
    $info mysql_fetch_array$data ))
     {
     Print 
    "<tr>";
     Print 
    "<td>" .$info['Name'] . "</td>";
     print 
    "<td>" .$info['Age'] . "</td>";
     print 
    "<td>" .$info['Height'] . "</td>";
     print 
    "</tr>"
     }
     Print 
    "</table>";
    ?>
    Thanks for helpin a noob guys.

  8. #8
    Join Date
    Aug 2009
    Posts
    593
    Wow, ok.

    This is more of what I meant earlier...

    PHP Code:
    //connects to mysql with "server", "user", "password" 
    $db_conn mysql_connect("localhost""root""") or die(mysql_error()); 
    //select the database "sam" 
    mysql_select_db("sam"$db_conn); 

    //sends a query to mysql with the "command" 
    $query "insert into text (Name, Age, Height) values 
    ('" 
    mysql_real_escape_string($Name$db_conn) . "', 
    '" 
    mysql_real_escape_string($Age$db_conn) . "', 
    '" 
    mysql_real_escape_string($Height$db_conn) . "') 
    "
    ;

    //tells mysql to do the query 
    mysql_query($query) or die (mysql_error()); 

  9. #9
    Join Date
    Mar 2010
    Posts
    672
    Don't add mysql_connect ("localhost", "root", "") into every mysql_real_escape_string(). Instead create a single handle from the original connection and use that:

    PHP Code:
    <?php
    //Takes the post from insert.php and sends it to mysql
    $Name $_POST['Name'];
    $Age = (int) $_POST['Age'];
    $Height $_POST['Height'];
    //connects to mysql with "server", "user", "password"
      
    $dbconn=mysql_connect ("localhost""root""")
       or die(
    mysql_error());
    //select the database "sam"
      
    mysql_select_db ("sam");
    //sends a query to mysql with the "command"
    $query="insert into text (Name, Age, Height) values
    ('" 
    mysql_real_escape_string($Name$dbconn) ."',
    '" 
    mysql_real_escape_string($Age$dbconn) ."',
      '" 
    mysql_real_escape_string($Height$dbconn) ."')
      "
    ;
    //tells mysql to do the query
    mysql_query($query)
      or die (
    mysql_error());
    echo 
    "Updated to: " .$Name" , " .$Age" , " .$Height ;
    ?>    

    <?php
    // Connects to your Database from mysql

    // Collects data from "text" table
    // Add a 'where' clause to only show a certain number of ppl on the table
    $data mysql_query("select * from text")
      or die(
    mysql_error());
    // Make the cells look good
    Print "<table border=2 cellpadding=3 cellspacing=2 width=20%>";
    /* Puts the "text" info into the $info array
    "while" makes the retrieve command a loop so it collects all data*/
    // Print out the contents of the entry
    {
    Print 
    "<tr>";
    Print 
    "<th>Name:</th>";
    Print 
    "<th>Age:</th>";
    Print 
    "<th>Height:</th>";
    Print 
    "</tr>";
    }
    while(
    $info mysql_fetch_array$data ))
    {
    Print 
    "<tr>";
    Print 
    "<td>" .$info['Name'] . "</td>";
    print 
    "<td>" .$info['Age'] . "</td>";
    print 
    "<td>" .$info['Height'] . "</td>";
    print 
    "</tr>";
    }
    Print 
    "</table>";
    ?>

  10. #10
    Join Date
    Aug 2009
    Posts
    593
    Snap :P

  11. #11
    Join Date
    Dec 2010
    Posts
    7
    Guys, thanks again for your time.
    But is there some kind of variable that needs to be places in mysql_real_escape_string?
    The same thing keeps happening. 5'5" becomes in the display table. But the "updated to" text says 5'5". I don't know what I'm doing wrong, but there's something. I followed ur direction to a T.

    BTW Mega Man is boss *thumbs up*

  12. #12
    Join Date
    Aug 2009
    Posts
    593
    I cleaned up your code the way I like it, I really hope it works :P
    Just make sure you have the correct data types in your text table, and enough room for the strings...

    Eg.

    Code:
    Name: varchar(255), Age: int(10), Height: varchar(10)
    PHP Code:
    <?php 

        
    //Takes the post from insert.php and sends it to mysql 
        
    $Name   $_POST['Name']; 
        
    $Age    = (int)$_POST['Age']; 
        
    $Height $_POST['Height']; 

        
    //connects to mysql with "server", "user", "password" 
        
    $db_conn mysql_connect('localhost''root''') or die(mysql_error()); 

        
    //select the database "sam" 
        
    mysql_select_db('sam'$db_conn); 

        
    //sends a query to mysql with the "command" 
        
    $query 'INSERT INTO `text` (`Name`, `Age`, `Height`) VALUES ("' mysql_real_escape_string($Name$db_conn) . '",' mysql_real_escape_string($Age$db_conn) . ',"' mysql_real_escape_string($Height$db_conn) . '")'

        
    //tells mysql to do the query 
        
    mysql_query($query$db_conn) or die (mysql_error()); 

        echo 
    'Updated to: ' $Name ' , ' $Age ' , ' $Height

    ?>     

    <?php 

        
    // Connects to your Database from mysql 
        // Collects data from "text" table 
        // Add a 'where' clause to only show a certain number of ppl on the table 
        
    $data mysql_query('SELECT * FROM `text`'$db_conn) or die(mysql_error()); 

        
    // Make the cells look good 
        
    echo '<table border="2" cellpadding="3" cellspacing="2" width="20%">'

        
    /* Puts the "text" info into the $info array 
        "while" makes the retrieve command a loop so it collects all data*/ 
        // Print out the contents of the entry 
        
    echo
            
    '<tr>' 
                
    '<th>Name:</th>' 
                
    '<th>Age:</th>' .
                
    '<th>Height:</th>' 
            
    '</tr>'

        while (
    $info mysql_fetch_array($data)) { 
            echo
                
    '<tr>' .
                    
    '<td>' htmlentities($info['Name'])   . '</td>' 
                    
    '<td>' htmlentities($info['Age'])    . '</td>' .
                    
    '<td>' htmlentities($info['Height']) . '</td>' 
                
    '</tr>'
        } 
        echo 
    '</table>';

    ?>
    I really like Samus as well, but people might think I'm weird
    Last edited by thraddash; 12-29-2010 at 06:37 PM. Reason: Added database part

  13. #13
    Join Date
    Dec 2010
    Posts
    7
    Yes! that's what it was. I guess I had the "height" column as (int) in my database. When I changed it to varchar it worked.
    Also, I changed the "Print" to "echo" cuz it looks a lot better.


    Haha, guy after my own heart. Samus is boss too. Gonna go in the the Varia suit next anime expo, tell me ppl won't think I'M weird.
    Link is my absolute favorite though.

  14. #14
    Join Date
    Aug 2009
    Posts
    593
    Zero Suit is my fav, but Varia is fine if your not a smoking hot babe :P

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles