Correct syntax for htmlspecialchars(), ENT_QUOTES inside Values ($data);

IBMKenobi
IBMKenobi used Ask the Experts™
on
I need to ensure punctuation marks are converted to HTML characters so I can run a one off script  (an importer) using data in a spreadsheet.

The code is below. I want all $data[] variables punctuation to be converted to html codes so that punctuation marks don't cause errors in my script and so they display correctly on my website when called from the database.

I have used this in the past: htmlspecialchars($data[1]), ENT_QUOTES
but I don't know the syntax for this particular format of code.

Thank you!

 
$import="INSERT into venue(name,address1,address2,telephone,email,website) values('$data[0]','$data[1]','$data[2]','$data[5]','$data[6]','$data[7]')

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try using mysql_real_escape_string function: example:


$query = sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) values('%s','%s','%s','%s','%s','%s')",
                  mysql_real_escape_string($data[0),
                  mysql_real_escape_string($data[1),
                  mysql_real_escape_string($data[2),
                  mysql_real_escape_string($data[5),
                  mysql_real_escape_string($data[6),
                  mysql_real_escape_string($data[7) );

Open in new window

Author

Commented:
Hi, sorry, I should have posted the whole code. I have inserted your new code as below with this error:
Parse error: syntax error, unexpected T_STRING in URL/importer.php on line 24
(line 24 is WHERE NOT EXIST)

while (($data = fgetcsv($file, ",")) !== FALSE) {
      $import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) values('%s','%s','%s','%s','%s','%s')",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) )

      WHERE NOT EXIST (
      SELECT name FROM venue
      WHERE
       name = '$data[0]'
       AND address1 = '$data[1]'
       AND address2 = '$data[2]'
       AND telephone = '$data[5]'
       AND email = '$data[6]'
       AND website = '$data[7]'
      )
";
The correct syntax is shown at  http://www.php.net/htmlspecialchars but it basically is

 htmlspecialchars($data[1] , ENT_QUOTES );

rather than

 htmlspecialchars($data[1]), ENT_QUOTES

In any case it is used for displaying things on the screen. For inserting into the database then mysql_real_escape_string is needed (see above althought the examples given are missing a closing ] on each line - $data[0] rather than $data[0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

@IBMKenobi - looks like we've been doing some simultaneous posting ;-)

Commented:

    while (($data = fgetcsv($file, ",")) !== FALSE) {
      $import = sprintf( "INSERT into venue(name,address1,address2,telephone,email,website)
                            values('%s','%s','%s','%s','%s','%s') WHERE NOT EXIST (
                                SELECT name FROM venue WHERE name = '%s' AND address1 = '%s' AND address2 = '%s'
                                    AND telephone = '%s' AND email = '%s' AND website = '%s'
                            )",
                        mysql_real_escape_string($data[0]),
                        mysql_real_escape_string($data[1]),
                        mysql_real_escape_string($data[2]),
                        mysql_real_escape_string($data[5]),
                        mysql_real_escape_string($data[6]),
                        mysql_real_escape_string($data[7]),
                        mysql_real_escape_string($data[0]),
                        mysql_real_escape_string($data[1]),
                        mysql_real_escape_string($data[2]),
                        mysql_real_escape_string($data[5]),
                        mysql_real_escape_string($data[6]),
                        mysql_real_escape_string($data[7]) )

       //Execute Query $import
       //
       //
       
     }//End-While

Open in new window

Author

Commented:
Having no luck here:

Parse error: syntax error, unexpected T_IF in URL\importer.php on line 35
Line 35: if ( @mysql_query($import) && (mysql_affected_rows() > 0)) {

I suspect this is an error from the earlier code, i.e. line 35 works fine, as the script works as long as there's no punctuation marks in the data.

Thanks.
while (($data = fgetcsv($file, ",")) !== FALSE) {
	$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) 
						values('%s','%s','%s','%s','%s','%s') 
WHERE NOT EXIST (
SELECT name FROM venue 
WHERE
name = '$s'
AND address1 = '%s'
AND address2 = '%s'
AND telephone = '%s'
AND email = '%s'
AND website = '%s')",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) )

if ( @mysql_query($import) && (mysql_affected_rows() > 0)) {

Open in new window

Commented:
just add ; at the end of line 33 in your code

 mysql_real_escape_string($data[7]) );

Commented:
also note, line 22 you are using $s whicj is wrong, you should use %s

name = '$s'
should be:
name = '%s'

Author

Commented:
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in URL\htdocs\cms\importer.php on line 40

Thanks again for your patience, I've raised the points to reflect this hopefully.

Commented:
mmm, what's on line 40 ? :)

mostly, you forget a semicolon ; on line 39

Author

Commented:
The rest of the script works when lines 15-35 were the original code (as long as the data being received had no punctuation) so I don't think the error is really on line 40 but I have posted the entire script for handiness.
<?PHP include 'dbconnection.php'; ?>

<?PHP
echo ('Opening File...<br />');
$file = fopen("gfaddresslist1.csv","r") or exit("Unable to open file!");

while (($data = fgetcsv($file, ",")) !== FALSE) {
	$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) 
						values('%s','%s','%s','%s','%s','%s') 
							WHERE NOT EXIST (
							SELECT name FROM venue 
							WHERE
	 						name = '%s'
	 						AND address1 = '%s'
	 						AND address2 = '%s'
	 						AND telephone = '%s'
	 						AND email = '%s'
	 						AND website = '%s'
							)",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) );

if ( @mysql_query($import) && (mysql_affected_rows() > 0)) {

		echo ('<span class="resultgood">Venue Added Successfully!</span><br />');
	}
		else {
			echo ( '<span class="resultbad">Error submitting venue:' . mysql_error().die()'<br /></span>' );
		}	
	$venueID = mysql_insert_id(); //contains last ID created by AUTO_INCREMENT in venue table

		$sql_venuetype = "INSERT IGNORE INTO venuetypelookup
							SET venueID=$venueID, venuetypeID=$data[4]";
				$ok1 = @mysql_query($sql_venuetype);
			if ($ok1) {
				echo ('<span class="resultgood">Venue Added to venuetypelookup Successfully!</span><br />');
			} else {
				echo ('Error inserting venueID and venuetypeID into venuetypelookup: '.mysql_error());
			}
		$sql_countylookup = "INSERT INTO countylookup 
							SET venueID=$venueID, countyID=$data[3]";
				if ( @mysql_query($sql_countylookup) ) {
				echo ('<span class="resultgood">Venue Added to countylookup Successfully!</span><br />');
			} else {
			echo ( '<span class="resultbad">Error submitting countylookup:' . mysql_error().'<br /></span>' );
			}
		$sql_ratinglookup = "INSERT INTO ratinglookup 
							SET venueID=$venueID";
				if ( @mysql_query($sql_ratinglookup) ) {
				echo ('<span class="resultgood">Venue Added to ratinglookup Successfully!</span><br />');
			} else {
			echo ( '<span class="resultbad">Error submitting to ratinglookup:' . mysql_error().'<br /></span>' );
			}
		$sql_paid = "INSERT INTO paid 
					SET venueID=$venueID, paid='0'";
				if ( @mysql_query($sql_paid) ) {
				echo ('<span class="resultgood">Venue Added to paid Successfully!</span><br />');
			} else {
			echo ( '<span class="resultbad">Error submitting to paid:' . mysql_error().'<br /></span>' );
			}
	mysql_query($import) or die(mysql_error());
}

if (feof($file)) echo ('End of file.'); 
fclose($file);
?>

Open in new window

Commented:
line 32 in the previous code, invalid use of die()

try:

else {
            die( '<span class="resultbad">Error submitting venue:' . mysql_error() . '<br /></span>' );
        }

Author

Commented:
I forgot about that, I left it in to stop the script going any further and entering venue data without the main venue going in correctly.

Result:
Opening File...

Warning: sprintf() [function.sprintf]: Too few arguments in URL\importer.php on line 33
Error submitting venue:Query was empty

Commented:
Note: some arguments passed to sprintf are missing, I didn't notice that in your code :)


while (($data = fgetcsv($file, ",")) !== FALSE) {
	$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website) 
						values('%s','%s','%s','%s','%s','%s') 
							WHERE NOT EXIST (
							SELECT name FROM venue 
							WHERE
	 						name = '%s'
	 						AND address1 = '%s'
	 						AND address2 = '%s'
	 						AND telephone = '%s'
	 						AND email = '%s'
	 						AND website = '%s'
							)",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]),
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) );

Open in new window

Author

Commented:
Error submitting venue: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 'WHERE NOT EXIST ( SELECT name FROM venue WHERE name = ' at line 3
Most Valuable Expert 2011
Top Expert 2016

Commented:
@IBMKenobi: Are you sure you don't want to hire a professional developer to give you a hand with this?  Parse errors are pretty basic issues in PHP, and SQL syntax errors are pretty basic issues in MySQL.  A pro should be able to get your app up and running in minutes.

If you want to post the COMPLETE code set as it exists now, I'll be glad to take a look.  Regards, ~Ray

Commented:
The following will only work on mysql 5
$import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website)
                        select '%s','%s','%s','%s','%s','%s' from dual
                            WHERE NOT EXIST (
                            SELECT name FROM venue
                            WHERE
                             name = '%s'
                             AND address1 = '%s'
                             AND address2 = '%s'
                             AND telephone = '%s'
                             AND email = '%s'
                             AND website = '%s'
                            )",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]),
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) );

Open in new window

Author

Commented:
Hi Ray,

I would be happy to hire a pro programmer to complete this site as I've built the rest of the site and there are other issues needing fixed. This section of code was created for me in EE so I don't want to go messing with it and end up making things worse at the moment though. I also pay for my subscription to EE so I'm hoping to get these final issues sorted here.

Thanks,
Mike

Author

Commented:
My server has PHP Version 5.2.6

Commented:
I meant the MySQL server version not the PHP version.

Conditional inserts using the above syntax requires MySQL server v5

Author

Commented:
Hi, sorry, I've just checked with my host and it's MySQL 5. Thanks.
Commented:
Great! so the last code should fix the problem.

here's the full code:


<?PHP include 'dbconnection.php'; ?>

<?PHP
echo ('Opening File...<br />');
$file = fopen("gfaddresslist1.csv","r") or exit("Unable to open file!");

while (($data = fgetcsv($file, ",")) !== FALSE) {
    $import=sprintf( "INSERT into venue(name,address1,address2,telephone,email,website)
                        select '%s','%s','%s','%s','%s','%s' from dual
                            WHERE NOT EXIST (
                            SELECT name FROM venue
                            WHERE
                             name = '%s'
                             AND address1 = '%s'
                             AND address2 = '%s'
                             AND telephone = '%s'
                             AND email = '%s'
                             AND website = '%s'
                            )",
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]),
                  mysql_real_escape_string($data[0]),
                  mysql_real_escape_string($data[1]),
                  mysql_real_escape_string($data[2]),
                  mysql_real_escape_string($data[5]),
                  mysql_real_escape_string($data[6]),
                  mysql_real_escape_string($data[7]) );

if ( @mysql_query($import) && (mysql_affected_rows() > 0)) {

        echo ('<span class="resultgood">Venue Added Successfully!</span><br />');
    }
        else {
            die ( '<span class="resultbad">Error submitting venue:' . mysql_error() . '<br /></span>' );
        }
    $venueID = mysql_insert_id(); //contains last ID created by AUTO_INCREMENT in venue table

        $sql_venuetype = "INSERT IGNORE INTO venuetypelookup
                            SET venueID=$venueID, venuetypeID=$data[4]";
                $ok1 = @mysql_query($sql_venuetype);
            if ($ok1) {
                echo ('<span class="resultgood">Venue Added to venuetypelookup Successfully!</span><br />');
            } else {
                echo ('Error inserting venueID and venuetypeID into venuetypelookup: '.mysql_error());
            }
        $sql_countylookup = "INSERT INTO countylookup
                            SET venueID=$venueID, countyID=$data[3]";
                if ( @mysql_query($sql_countylookup) ) {
                echo ('<span class="resultgood">Venue Added to countylookup Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting countylookup:' . mysql_error().'<br /></span>' );
            }
        $sql_ratinglookup = "INSERT INTO ratinglookup
                            SET venueID=$venueID";
                if ( @mysql_query($sql_ratinglookup) ) {
                echo ('<span class="resultgood">Venue Added to ratinglookup Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting to ratinglookup:' . mysql_error().'<br /></span>' );
            }
        $sql_paid = "INSERT INTO paid
                    SET venueID=$venueID, paid='0'";
                if ( @mysql_query($sql_paid) ) {
                echo ('<span class="resultgood">Venue Added to paid Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting to paid:' . mysql_error().'<br /></span>' );
            }
    mysql_query($import) or die(mysql_error());
}

if (feof($file)) echo ('End of file.');
fclose($file);
?>

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
Well, without your data base it may be hard for us to test, but like I said, "If you want to post the COMPLETE code set as it exists now,..."

However it looks like SysTurn is making good progress, too.

Author

Commented:
Hi SysTurn,

Thanks again for your patience so far. This still isn't working unfortunately.
I've rewritten it to try a 'simpler' format. i.e. noob

while (($data = fgetcsv($file, ",")) !== FALSE) {
    $data0 = mysql_real_escape_string($data[0]);
    $data1 = mysql_real_escape_string($data[1]);
    $data2 = mysql_real_escape_string($data[2]);
    $data5 = mysql_real_escape_string($data[5]);
    $data6 = mysql_real_escape_string($data[6]);
    $data7 = mysql_real_escape_string($data[7]);
   

    $import = "INSERT INTO venue(name,address1,address2,telephone,email,website)
                        VALUES ($data0,$data1,$data2,$data5$,$data6,$data7)";

This is the data from the CSV file: (the empty line is an empty field in that particular address)
Richy's Bar & Bistro
                   
                   Clonakilty
                   353 (0) 23 21852
                   richysbarandbistro@eircom.net
                   www.richysbarandbistro.com

The error displayed is below:
Error submitting venue: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 '\'s Bar &  Bistro,,Clonakilty,353 (0) 23 21852$,richysbarandbistro@eircom.net ,ww'  at line 2


Most Valuable Expert 2011
Top Expert 2016

Commented:
You need quotes around the $data values like this...

$import = "INSERT INTO venue(name,address1)
                       VALUES ('$data0','$data1')";

Author

Commented:
We're soooo close.
The script now works, entering the correct data into the form with all punctuation converted to HTML codes.

The only error is the venue is entered twice. The other fields are entered once based on the first venue ID created by mysql_insert_id() which is correct.

The venue receives two identical rows however. I can delete them manually but when I run the full CSV file there are a couple of hundred entries.

The venue names aren't unique but I'm assuming the telephone numbers are unique, .e.g. a chain of food outlets.

Most Valuable Expert 2011
Top Expert 2016

Commented:
Let me try this again...

"If you want to post the COMPLETE code set as it exists now,..."

The reason I ask you for that is because I am just an expert, not a mind reader, and I cannot see your logic errors without seeing your code.

Also, UNIQUE is a term of art in data base processing.  An attempt to insert a duplicate value into a UNIQUE  column causes MySQL to throw error #1062.  You can test for this in mysql_errno() and recognize it.

Author

Commented:
Hi Ray,

I've re-attached the complete code but including the new while statement.

Thanks,
Mike


CSV file sample contents: black line is empty address1 field

Richy's Bar & Bistro
 
Clonakilty
353 (0) 23 21852
richysbarandbistro@eircom.net
www.richysbarandbistro.com

<?PHP include 'dbconnection.php'; ?>

<?PHP
echo ('Opening File...<br />');
$file = fopen("gfaddresslist1.csv","r") or exit("Unable to open file!");

while (($data = fgetcsv($file, ",")) !== FALSE) {
	$data0 = mysql_real_escape_string($data[0]);
	$data1 = mysql_real_escape_string($data[1]);
	$data2 = mysql_real_escape_string($data[2]);
	$data5 = mysql_real_escape_string($data[5]);
	$data6 = mysql_real_escape_string($data[6]);
	$data7 = mysql_real_escape_string($data[7]);
	
	echo ($data0.'<br />
			'.$data1.'<br />
			'.$data2.'<br />
			'.$data5.'<br />
			'.$data6.'<br />
			'.$data7.'<br />
			<br /><br />');
	
    $import = "INSERT INTO venue(name,address1,address2,telephone,email,website)
                        VALUES ('$data0','$data1','$data2','$data5','$data6','$data7')";

if ( @mysql_query($import) ) {

        echo ('<span class="resultgood">Venue Added Successfully!</span><br />');
    }
        else {
            die ( '<span class="resultbad">Error submitting venue:' . mysql_error() . '<br /></span>' );
        }
    $venueID = mysql_insert_id(); //contains last ID created by AUTO_INCREMENT in venue table

        $sql_venuetype = "INSERT IGNORE INTO venuetypelookup
                            SET venueID=$venueID, venuetypeID=$data[4]";
                $ok1 = @mysql_query($sql_venuetype);
            if ($ok1) {
                echo ('<span class="resultgood">Venue Added to venuetypelookup Successfully!</span><br />');
            } else {
                echo ('Error inserting venueID and venuetypeID into venuetypelookup: '.mysql_error());
            }
        $sql_countylookup = "INSERT INTO countylookup
                            SET venueID=$venueID, countyID=$data[3]";
                if ( @mysql_query($sql_countylookup) ) {
                echo ('<span class="resultgood">Venue Added to countylookup Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting countylookup:' . mysql_error().'<br /></span>' );
            }
        $sql_ratinglookup = "INSERT INTO ratinglookup
                            SET venueID=$venueID";
                if ( @mysql_query($sql_ratinglookup) ) {
                echo ('<span class="resultgood">Venue Added to ratinglookup Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting to ratinglookup:' . mysql_error().'<br /></span>' );
            }
        $sql_paid = "INSERT INTO paid
                    SET venueID=$venueID, paid='0'";
                if ( @mysql_query($sql_paid) ) {
                echo ('<span class="resultgood">Venue Added to paid Successfully!</span><br />');
            } else {
            echo ( '<span class="resultbad">Error submitting to paid:' . mysql_error().'<br /></span>' );
            }
    mysql_query($import) or die(mysql_error());
}

if (feof($file)) echo ('End of file.');
fclose($file);
?>

Open in new window

Most Valuable Expert 2011
Top Expert 2016

Commented:
Thanks - I'll have a look.  Can you please post the test data set that shows the error when I run that script?  

Author

Commented:
Hi Ray,

CSV file attached.
Just for better points allocation, am I better opening a new question ref double data entry?
That way I can give SysTurn points for his patience and replies and give yourself more points for sorting the double data entry?

Thanks,
Mike

gfaddresslist1.csv
Most Valuable Expert 2011
Top Expert 2016

Commented:
Looks like you run the $import query twice.  Lines 26 and 64.

This question was about HTMLSpecialChars() and that has been well answered, so I will sign off on this now.  Just be sure to use that function before you echo any of the data out of your data base and into the browser output stream.

Best regards, ~Ray

Author

Commented:
Thanks very much for your patience and time and to the other experts who all contributed great info.
Most Valuable Expert 2011
Top Expert 2016

Commented:
@IBMKenobi: You can split the points if you think that more than one of the Experts helped you.  Just a thought for going forward.  As I look at this thread, it looks to me like BPortlock answered your posted question first and best.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial