MYSQL_Query using textblock variables having errors with quotes?

I'm currently building a website for my friends and me, and I've decided to have the homepage use a news system. I have the page working with PHP to load articles from my MySQL Database, and that's working fine. My real issue is with the page that adds items to the MySQL database. I am using the normal query system ("INSERT INTO table VALUES ('', '', '', '', '')") but I'm using variables inside the query. The variables pull their values from a POST method on the same page, and it seems to run fine; except for the <textblock> part of the POST form. (I'll quote the code in a second, because I'm quite aware that what I say has a way of confusing even experts...) Whenever someone (me) writes anything in the textblock that requires a single or double quote, the SQL submission fails.
I figure that it has something to do with the fact that I've been testing it using single quotes (So the pattern goes " ' '), so I tried reversing the pattern of the query itself (' " ') and that worked. Issue there is that people won't be able to use double quotes then; or for example, both quotes at the same time.
What I reeeaally want is for the people to not need to escape the quotes (\' \"), but still be able to use both kinds.
The code for the submission form is currently:

<?php
if($_POST['submit']){
    $title = $_POST['title'];
    $content = $_POST['content'];
    $creator = $_POST['creator'];
    date_default_timezone_set('America/Vancouver');
    $date = date('F d, Y h:i:s a');
    if(strlen($content)>6000){
        echo "Article is too long.";
    } else {
        if(strlen($title)>60){
            echo "Title is too long.";
        } else {
            require "dbc.php"; //Connects to the user database
            $query = mysql_query("SELECT * FROM loginData WHERE username='$creator'");
            while($rows = mysql_fetch_assoc($query)){
                $dbusername = $rows['username'];
            }
            if($creator == $dbusername){
                require "ndbc.php"; //Connects to separate database for the news.*
                $query = mysql_query("INSERT INTO list VALUES ('', '$title', '$content', '$creator', '$date')") or die (mysql_error());
                die("Article Submission Complete! <a href='index.php'>Click here to return.</a>");
            } else echo "Username does not exist.";
        }
    }
}

// * In case anyone asks, I'm using a separate database for the news articles because I
//   plan on building a commenting system for each news item. Shouldn't prove too difficult.
?>

<html>

<form action="newNews.php" method="POST" />
Title: <input type="text" name="title" value="<?php echo "$title"; ?>" /><br />
Content: <textarea name="content" cols="40" rows="5"><?php echo "$content" ?></textarea><br />
Creator: <input type="text" name="creator" value="<?php echo "$username"; ?>" /><br />
<input type="submit" name="submit" value="Submit" />
</form>

</html>

Also; if anyone wants to see the issue firsthand on the site, the address is http://chatline.ennilla.com/ and the page for submitting articles is http://chatline.ennilla.com/newNews.php . Because the system currently only requires you to have a valid ID for creating posts, just use 'wildpin' (no quotes) because the sign-up page is currently damaged as well. As a warning though, there is currently a demo of the Java Live chat system I'll be implementing later on the main page. It'll give a pop up, but you can just ignore it. Nobody'll be using it.

3

Contributors

6

Replies

1 Day

Discussion Span

6 Months Ago

Last Updated

7

Views

Question
Answered

eNnilla

Newbie Poster

3 posts since Nov 2012

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

6 Months Ago

2

use mysql_real_escape_string() function to escape characters in all input fields. Not just to enable people to enter quotes but also to prevent evil people to enter harmfull code.

$title = mysql_real_escape_string($_POST['title']);
$content = mysql_real_escape_string($_POST['content']);
$creator = mysql_real_escape_string($_POST['creator']);

Mind you the connection to mysql has to be established in order to use this function. BTW: It is recommended to switch to mysqli extension.

broj1

Nearly a Posting Virtuoso

1,211 posts since Jan 2011

Reputation Points: 167

Solved Threads: 164

Skill Endorsements: 13

6 Months Ago

0

Thanks broj1, but that only half worked?
Once I put in the code and ran the page, it came back with the following errors:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ennilla'@'localhost' (using password: NO) in /home/ennilla/public_html/chatline/newNews.php on line 3

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/ennilla/public_html/chatline/newNews.php on line 3

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ennilla'@'localhost' (using password: NO) in /home/ennilla/public_html/chatline/newNews.php on line 4

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/ennilla/public_html/chatline/newNews.php on line 4

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ennilla'@'localhost' (using password: NO) in /home/ennilla/public_html/chatline/newNews.php on line 5

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/ennilla/public_html/chatline/newNews.php on line 5
Article Submission Complete! Click here to return.

The connection to the database was successful, and the artice was added, but the only part that was actually put into the database was the date...

eNnilla

Newbie Poster

3 posts since Nov 2012

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

6 Months Ago

0

you need to connect to the database before that function is available:
broj1 said:
Mind you the connection to mysql has to be established in order to use this function. BTW: It is recommended to switch to mysqli extension.

move line 14 in your file to line 3:
require "dbc.php"; //Connects to the user database
then try to use mysql_real_escape_string

ddymacek

Posting Whiz

324 posts since Jun 2010

Reputation Points: 38

Solved Threads: 68

Skill Endorsements: 0

6 Months Ago

0

Okay, thanks. That works perfectly now. I suppose that means I'll need to apply the same adjustments to the display page?

eNnilla

Newbie Poster

3 posts since Nov 2012

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

6 Months Ago

0

You have to validate/sanitize input values whenever you either store them in a db or use them in javascript, HTML, CSS, email or any other possible context. If you forgot to do it you risk your app being compromited by SQL injection, XSS attack and similar. Sanitizing means basically escaping or replacing the characters that are unwanted in a particular context like:

if you intend to store user entered data into database then most unwanted character is ' (single quote). If you forget to escape it the bad guy can enter code that will change your SQL statemnt in a way that you do not want.
if you intend to use user entered data in your html then for example you do not want characters < and > and sometimes & get into html since they can be used to insert harmful client side script code into html
etc...

See nice articles here and here and google for sql injection, XSS, html injection...

broj1

Nearly a Posting Virtuoso

1,211 posts since Jan 2011

Reputation Points: 167

Solved Threads: 164

Skill Endorsements: 13

6 Months Ago

0

And the same goes for cookie values. Sanitize them before you use them.

broj1

Nearly a Posting Virtuoso

1,211 posts since Jan 2011

Reputation Points: 167

Solved Threads: 164

Skill Endorsements: 13

Question Answered as of 6 Months Ago by broj1 and ddymacek

MYSQL_Query using textblock variables having errors with quotes?

This question has already been solved: Start a new discussion instead