Escape Strings

[dpiearcy]

This script works when I'm adding a new row to the database:

 

$request = mysqli_real_escape_string($myConnection, $request);


$query = mysqli_query($myConnection, "INSERT INTO table (name, email, phone, share, request, sendmail, date) 
       VALUES('$name', '$email', '$phone', '$share', '$request', '$sendmail', now())") or die (mysqli_error($myConnection));

 

But when I update this code does not work. Gives me an error.

 

$request = mysqli_real_escape_string($myConnection, $request);


$query = mysqli_query($myConnection, "UPDATE table SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

 

If I add the following to variables:

 

$var= stripslashes(trim($_POST['var']));
$var = mysqli_real_escape_string($var);

 

It WILL go ahead and go. But then nothing is written to the database. Trying to get them dang ' things to go thru and usually the mysqli_real_escap_string does the trick. At least on Insert. Seems not to on update.

 

Suggestions?

[Jessica]

Is your table actually named table? Please post the error and your actual code.

[dpiearcy]

Well no...it's not actually called table. Not sure why that was the only thing I changed in there :-) That is the actual code except the table is called prayer.

 

You want the whole code? I figured that was the important part.

 

Oh. And thanks for being nice Jessica. I've seen some of your replies to others. But I have to admit, I love your sense of humor. I can't remember which one or what you said the other day that made me laugh out loud but I was thinking the same thing.

[dpiearcy]

Is your table actually named table? Please post the error and your actual code.

 

Oh... and as I said at the bottom there... if I add that last bit of script it will go ahead and not throw an error but nothing gets written to the database.

 

Without that code it doesn't give me a specific error (need to learn that command for it to tell me the error. I'll put that on my to do list) just says there's something wrong near ' (the word with the ' in it) and check my manual.

[Christian F.]

There are a couple of issues still:

1. You've yet to post the actual error message, as requested by Jessica. Without it we're just fumbling around blind, which is why we request them (and why they're shown in the first place).
   
2. Where does $var enter into the picture? It's not used in the query...
   
3. You should not use stripslashes () on the input value, at least not without checking if "magic quotes" have been enabled first. Using stripslashes () on input without this can introduce subtle bugs, and in the worst case allow for an attacker to launch a successful attack on your site because of it.

 

One tip I'll leave you with: Whenever you echo out the error message for debugging, include the generated SQL query. Most of the time, it'll help shed a lot of light on the actual problem.

Edited December 17, 2012 by Christian F.

[dpiearcy]

One tip I'll leave you with: Whenever you echo out the error message for debugging, include the generated SQL query. Most of the time, it'll help shed a lot of light on the actual problem.

 

Yeah, that was the thing I mentioned I didn't know how to do. From what I've read I add this code?

 

ini_set('display_errors',1); 
error_reporting(E_ALL);

 

Does this go in my query string? If so can you post me an example?

 

I'll post the full code without me changing variable names. But for now $var was actually $praise. I'll get rid of the strip slashes and then copy and paste the code here. Sorry for making changes to the actual code. I can see how that can become confusing.

[Christian F.]

The links in Jessica's signature explains all that in great detail, so I recommend reading them.

[mrMarcus]

Well no...it's not actually called table. Not sure why that was the only thing I changed in there :-) That is the actual code except the table is called prayer.

 

You want the whole code? I figured that was the important part.

 

Oh. And thanks for being nice Jessica. I've seen some of your replies to others.

 

Hehe, you got them shaking in their boots, Jessica.

 

@diearcy - don't change variable names, table names, etc.  Only thing you can omit/alter is connection credentials.  Even still, without a link to your domain, db credentials are useless.

 

Once you have changed your code back to the original, I will also have a look at it.

[Jessica]

*shrug* It's not my fault that the last few generations have been so pampered and babied that they interpret emotionless facts as "not nice" and can't handle it when someone actually is being "not nice". Waaah self-confidence, Waaaah offended, Waaaah judging. Waaaaah grow up and stop getting so butt hurt. 

 

Op: read the links in my signature about SQL and errors. (Or all of them ) 

[dpiearcy]

*shrug* It's not my fault that the last few generations have been so pampered and babied that they interpret emotionless facts as "not nice" and can't handle it when someone actually is being "not nice". Waaah self-confidence, Waaaah offended, Waaaah judging. Waaaaah grow up and stop getting so butt hurt.

 

Op: read the links in my signature about SQL and errors. (Or all of them )

 

I'll read your links. Thanks. And I don't interpret your facts that way. I said I like them :-) And I'm sure I'm older than you. Not sure what difference that makes other than I'm OLD for a computer geek :-)

[Jessica]

I'll read your links. Thanks. And I don't interpret your facts that way. I said I like them :-) And I'm sure I'm older than you. Not sure what difference that makes other than I'm OLD for a computer geek :-)

It wasn't a rant about you.

[dpiearcy]

Hehe, you got them shaking in their boots, Jessica.

 

@diearcy - don't change variable names, table names, etc. Only thing you can omit/alter is connection credentials. Even still, without a link to your domain, db credentials are useless.

 

Once you have changed your code back to the original, I will also have a look at it.

 

No worries with log in credentials anyway. That's all done with a require script anyway so isn't located in my query string other than the call for the other script. I'll change the code back and post ASAP.

[dpiearcy]

Ok. Here's the actual code (after a bunch of if and else if statements occur) but here's the part that writes to the database and this works fine. I can use a word like don't and it's ok with the '.

 

include_once "Scripts/connect_to_mysql.php";
$request = mysqli_real_escape_string($myConnection, $request);
$query = mysqli_query($myConnection, "INSERT INTO prayer (name, email, phone, share, request, sendmail, date) 
       VALUES('$name', '$email', '$phone', '$share', '$request', '$sendmail', now())") or die (mysqli_error($myConnection));

header("location: prayer_wall.php");
exit();}

 

BUT... the same does not work for update. Here's that code:

 

$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);
include_once "Scripts/connect_to_mysql.php";
$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

echo 'Operation Completed Successfully! <br /><br />';
header("location: praise_wall.php");
exit();

 

Yes... I know there are two escape strings. Just using the single one wasn't working so I added the var specific one too.

 

I just read the link in Jessica's footer about error reporting and will amend so I can give you the actual report. This will follow but maybe this will get you started thinking.

[Jessica]

Yes... I know there are two escape strings. Just using the single one wasn't working so I added the var specific one too.

 

This doesn't make sense, you're NOT escaping twice.

 

However, look at what you are doing different between insert and updated.

1. $request = mysqli_real_escape_string($myConnection, $request);

2. $praise = mysqli_real_escape_string($praise);

 

What's different about these?

[dpiearcy]

Actually Jessica after reading your link am I right in assuming if I add

 

, E_USER_ERROR)

 

to my code I'm using already

 

$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

 

and stick it behind (mwsqli_error($myConnection)) it will give me a more detailed error report???

[mrMarcus]

If you simply echo out your query, do you see expected results?

 

$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);
include_once "Scripts/connect_to_mysql.php";

$query = "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'";
echo $query; exit(0);

[dpiearcy]

This doesn't make sense, you're NOT escaping twice.

 

However, look at what you are doing different between insert and updated.

1. $request = mysqli_real_escape_string($myConnection, $request);

2. $praise = mysqli_real_escape_string($praise);

 

What's different about these?

 

Oh. Yeah I see that. I was actually using the same string for update as insert and it didn't work that's when I switched to var specific. Then had it twice so I removed the other string that was identical to the insert.

 

I'll change it back to that way and give it another go.

[dpiearcy]

If you simply echo out your query, do you see expected results?

 

$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);
include_once "Scripts/connect_to_mysql.php";

$query = "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'";
echo $query; exit(0);

 

I haven't been echoing it out. I just look over in php myadmin and see nothing was written. Same effect. Except that annoying logging me out after 1800 seconds :-)

[mrMarcus]

 

 

I haven't been echoing it out. I just look over in php myadmin and see nothing was written. Same effect. Except that annoying logging me out after 1800 seconds :-)

 

You need to have error_reporting turned on because it'd be telling you that you're establishing a db connection after you've called mysqli_real_escape_string().  It relies on an open connection to your db to work.  In your INSERT query you have done so correctly.  In your UPDATE, not so much.

 

<?php
$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);

include_once "Scripts/connect_to_mysql.php";                 // ME!  I'M THE PROBLEM!! MOVE ME UP TO THE TOP (BEFORE mysqli_real_escape_string())

$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));
 
echo 'Operation Completed Successfully! <br /><br />';
header("location: praise_wall.php");
exit();

Edited December 17, 2012 by mrMarcus

[dpiearcy]

Ok. Changed it back Jessica. Now this

 

$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);


include_once "Scripts/connect_to_mysql.php";
$request = mysqli_real_escape_string($myConnection, $request);
$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

header("location: praise_wall.php");
exit();

 

Will not write to the database at all.

 

THIS:

 

$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
include_once "Scripts/connect_to_mysql.php";
$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

header("location:praise_wall.php");
exit();

 

Will update. but will of course give me this error if I use a word like don't.

 

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 'T', answerdate = NOW() WHERE id = '12'' at line1

 

And finally, This:

 

$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);[/size][/font][/color]
[color=#000000][font=Times][size=1]include_once "Scripts/connect_to_mysql.php";
$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

header("location: praise_wall.php");
exit();

Will not write regardless of '

Edited December 17, 2012 by dpiearcy

[dpiearcy]

You need to have error_reporting turned on because it'd be telling you that you're establishing a db connection after you've called mysqli_real_escape_string(). It relies on an open connection to your db to work. In your INSERT query you have done so correctly. In your UPDATE, not so much.

 

<?php
$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);

include_once "Scripts/connect_to_mysql.php";                 // ME!  I'M THE PROBLEM!! MOVE ME UP TO THE TOP (BEFORE mysqli_real_escape_string())

$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

echo 'Operation Completed Successfully! <br /><br />';
header("location: praise_wall.php");
exit();

 

actually I moved it up and this:

 

include_once "Scripts/connect_to_mysql.php";
$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);
$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));

header("location:praise_wall.php");
exit();

does not write to database. (dang it. Just realized I haven't been removing that URL earlier :-) oops.

 

Nor does it work if I move the require connection just below my post variable settings and just above the escape string.

[mrMarcus]

It *will* once you sort out the error with your query: 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 'T', answerdate = NOW() WHERE id = '12'' at line1

 

Echo you query, like I said, and post here.

 

And it doesn't matter where you make the connection as long as it precedes any functions that rely on it, ie. mysqli_real_escape_string()

 

EDIT: what is the column type of `answerdate`?  I'm thinking you have it set to varchar or something of the like which would be a problem.

Edited December 17, 2012 by mrMarcus

[PFMaBiSmAd]

mysqli_real_escape_string REQUIRES the mysqli link resource as the first parameter. Your code would be producing an error at that statement if your error_reporting was set to E_ALL and display_errors was set to ON.

 

Don't go any further until you set the error_reporting/display_errors settings as suggested.

[dpiearcy]

It *will* once you sort out the error with your query: 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 'T', answerdate = NOW() WHERE id = '12'' at line1

 

Echo you query, like I said, and post here.

 

And it doesn't matter where you make the connection as long as it precedes any functions that rely on it, ie. mysqli_real_escape_string()

 

EDIT: what is the column type of `answerdate`? I'm thinking you have it set to varchar or something of the like which would be a problem.

It's a date type

[dpiearcy]

mysqli_real_escape_string REQUIRES the mysqli link resource as the first parameter. Your code would be producing an error at that statement if your error_reporting was set to E_ALL and display_errors was set to ON.

 

Don't go any further until you set the error_reporting/display_errors settings as suggested.

 

Then help me out with the error reporting. I tried this and just got a white screen:

 

include_once "Scripts/connect_to_mysql.php";
$name = $_POST['name'];
$praise = $_POST['praise'];
$pid = $_POST['pid'];
$praise = mysqli_real_escape_string($praise);
$query = mysqli_query($myConnection, "UPDATE prayer SET how = '$praise', answerdate = NOW() WHERE id = '$pid'") or die (mysqli_error($myConnection));
$result = mysqli_query($query) or trigger_error("Query Failed! SQL: $query - Error: ".mysqli_error(), E_USER_ERROR);
header("location: http://orchardmidland.com/NewOrchard/praise_wall.php");
exit();