Database ERROR

Schoorsteen 0 Light Poster · 2010-07-31T00:37:46+00:00

Hey there Terry,

Could we please see the code where you try to insert into the database?

Thanks!

terrymold 7 Light Poster · 2010-07-31T01:14:09+00:00

Hi

$connection = mysql_connect($host,$user,$pass) or die ("Unable to connect to Server!"); 
mysql_select_db($dbase, $connection) or die ("Unable to connect to Database!");

$sql = "INSERT INTO newsletter (pageTitle, headerLogo, volumeIssue, sermonDate, headerSubject, headerQuran, quranicVerse_1, " .
"quranicPicture_1, quranicVerse_2, quranicPicture_2, headerHadith, hadithText, hadithPicture, headerMessiah, messiahText, " .
"messiahPicture, sourceLink_1, headerMainSummary, mainSummaryText, headerMainText, mainText, mainPicture, sourceLink_2, " .
"mainSeparator, headerSpecial, specialNotes) " .
"VALUES ('$pageTitle','$headerLogo','$volumeIssue','$sermonDate','$headerSubject','$headerQuran','$quranicVerse_1'," .
"'$quranicPicture_1','$quranicVerse_2','$quranicPicture_2','$headerHadith','$hadithText','$hadithPicture','$headerMessiah'," .
"'$messiahText','$messiahPicture','$sourceLink_1','$headerMainSummary','$mainSummaryText','$headerMainText','$mainText'," .
"'$mainPicture','$sourceLink_2','$mainSeparator','$headerSpecial','$specialNotes')";

//execute SQL statement
$result = mysql_query($sql);

// check for error
if (mysql_error()) { print "Database ERROR: " . mysql_error(); }

I hope this is enough to see what's wrong. And just as a matter of interest, at home where I am writing these scripts, I use WAMP 2.0i and I have had no problems, no errors, it just works as I would expect it to.

But when I put everything online, I started getting all sorts of weird errors.
My MySQL is 5.1.36 and the Hosting Co is 5.0.77-log
My PHP is 5.3.0 and the Hosting Co is 5.2.6
Also, I've just realised that the Hosting Co system is Windows NT IIS6124 5.2 build 3790, so I don't know if any of that will help?

Thanks
Terry

Schoorsteen 0 Light Poster · 2010-07-31T01:22:19+00:00

You should use the [code\][\/code] blocks to improve the readability.

Anyhow, I see you simply place the variables in the query, without formatting them, because one of the variables got a ' in it, the whole code breaks.

You should for example call mysqli_real_escape_string on every variable, that would prevent such thing to happen. You could also use htmlspecialchars();. This prevents your database with getting injected. (MySQL Injection)

To learn more about SQL Injection: http://en.wikipedia.org/wiki/SQL_injection

Good luck!

hielo 65 Veteran Poster · 2010-07-31T02:25:26+00:00

For some reason, the error message doesn't have the full text (ending height with just heig)?

On the field in question, look at the size of the field in your db. In other words, look at the maximum number of fields it can accept. Now look at the text you are trying to insert for that field. Are you exceeding the limit? If so, you will need to increase the size of your field.

Schoorsteen 0 Light Poster · 2010-07-31T03:52:35+00:00

That should give another error hielo, not a syntax error. This error is most likely produced by not escaping the ' character.

terrymold 7 Light Poster · 2010-07-31T04:33:52+00:00

Hi again Schoorsteen
I fully understand that having a rogue character can certainly upset things somewhat, and I guess you also know that a programmer can barely find his own mistakes. And that's the case now. I have looked at every variable and I'm afraid I cannot see this ' character anywhere it's not supposed to be.
So would you please tell me where it is?

Thanks a million
Terry

terrymold 7 Light Poster · 2010-07-31T04:38:03+00:00

Hi all
Sorry but I had to go out for a bit.
I actually have looked at field sizes in the table before I posted the error, because that's exactly what I first thought. That it was cutting "height" a bit short.
Over the years, I've had plenty of typos. Sometimes they are easy to find, and sometimes not. Like this one!
Terry

terrymold 7 Light Poster · 2010-07-31T06:34:37+00:00

Hi all
It's just about time for bed, but before I go, I just wanted to pass on a few interesting things I've found out while trying to fix this problem.

Following Schoorsteen's advice I had a look at mysqli_real_escape_string, but it looked a bit too complicated for what I was trying to do.

Then I had a look at HTMLSPECIALCHARS() and thought that it just might do the trick. But I didn't get very far because as soon as the script was run, it came up with a Fast CGI error.

In all this testing and whatever, I actually had 16 lines of variables which mostly needed absolute image addresses and as these generally begin with "<img src= and end with >" then all the bits inbetween have to be enclosed with single quotes.

And that while Wamp and my server readily accepts all this, it seems that the IIS server of our host doesn't like all those single quotes, but only if they are in variables. If you look at my SQL variable in a previous post for this problem, you will notice that there are lots of single quotes. And the IIS server doesn't mind them.

But ALL the single quotes in the above-mentioned variables had to be ADDSLASHed. And now it works perfectly.

While this post should now be closed because it is solved, I would still very much like to know why all this happened. Like could it really be due to the IIS server? Should we switch to a Linux server? Even though I'm by no means an expert programmer, I do like to know why something doesn't work, and why it was fixed.

Regards
Terry

Schoorsteen 0 Light Poster · 2010-07-31T11:05:14+00:00

Addslashes is quite close to mysqli_real_escape_string, at first I wanted to give you that function, however I read on the PHP doc that it's better to use mysqli_real_escape string. I always used addslashes and never got a mysql injection, so you don't have to worry about that. Good call!

Anyhow, on a Linux server you would get the same result. It's just that your query will become something like:

INSERT INTO table (field1, field2) VALUES ('<img src='image.jpg'>', '<img src='anotherimage.jpg'>');

You see how it breaks the query? That's a crossplatform fault, and I can not tell you why it worked on your WAMP server and it didn't on your IIS server. I do know that this is the right way of building your query though!

Happy I could help!

Schoorsteen

manzarr 1 Light Poster · 2010-07-31T12:13:22+00:00

can you show your full code, how to you insert values in database
it may be some error show due to header so when you use header
type top of page
<?php
ob_start();
?>
it remove all headers error

hielo 65 Veteran Poster · 2010-08-03T20:11:42+00:00

try:

<?php
$connection = mysql_connect($host,$user,$pass) or die ("Unable to connect to Server!");
mysql_select_db($dbase, $connection) or die ("Unable to connect to Database!");

$sql = sprintf("INSERT INTO newsletter (pageTitle, headerLogo, volumeIssue, sermonDate, headerSubject, headerQuran, quranicVerse_1, 
								quranicPicture_1, quranicVerse_2, quranicPicture_2, headerHadith, hadithText, hadithPicture, headerMessiah, messiahText, 
								messiahPicture, sourceLink_1, headerMainSummary, mainSummaryText, headerMainText, mainText, mainPicture, sourceLink_2, 
								mainSeparator, headerSpecial, specialNotes) 
			VALUES (	'%s','%s','%s','%s','%s','%s','%s',
					'%s','%s','%s','%s','%s','%s','%s',
					'%s','%s','%s','%s','%s','%s','%s',
					'%s','%s','%s','%s','%s')

               ,mysql_real_escape_string( $pageTitle )
               ,mysql_real_escape_string( $headerLogo )
               ,mysql_real_escape_string( $volumeIssue )
               ,mysql_real_escape_string( $sermonDate )
               ,mysql_real_escape_string( $headerSubject )
               ,mysql_real_escape_string( $headerQuran )
               ,mysql_real_escape_string( $quranicVerse_1 )
               
               ,mysql_real_escape_string( $quranicPicture_1 )
               ,mysql_real_escape_string( $quranicVerse_2 )
               ,mysql_real_escape_string( $quranicPicture_2 )
               ,mysql_real_escape_string( $headerHadith )
               ,mysql_real_escape_string( $hadithText )
               ,mysql_real_escape_string( $hadithPicture )
               ,mysql_real_escape_string( $headerMessiah )
               ,mysql_real_escape_string( $messiahText )
               
               ,mysql_real_escape_string( $messiahPicture )
               ,mysql_real_escape_string( $sourceLink_1 )
               ,mysql_real_escape_string( $headerMainSummary )
               ,mysql_real_escape_string( $mainSummaryText )
               ,mysql_real_escape_string( $headerMainText )
               ,mysql_real_escape_string( $mainText )
               ,mysql_real_escape_string( $mainPicture )
               ,mysql_real_escape_string( $sourceLink_2 )
               
               ,mysql_real_escape_string( $mainSeparator )
               ,mysql_real_escape_string( $headerSpecial )
               ,mysql_real_escape_string( $specialNotes )
		);
//execute SQL statement
$result = mysql_query($sql);

// check for error
if (mysql_error()) { print "Database ERROR: " . mysql_error(); }
?>