[SOLVED] mysql_real_escape_string or equiv in sql?

[The14thGOD]

I have a CMS that has a preview function which is basically a clone of the live data unless someone saves a draft. When they decide they want to make the draft live (published) I'm running into a small problem.

 

I escaped the data when inserted into the draft table, so when I pull that data and put it into a query to update the live table it breaks because the data is not escaped. (magic quotes disabled).

 

Is there an easy way to use an equivalent function of mysql_real_escape_string in a query to escape all the fields or do I just have to resort to going in and adding mysql_real_escape_string to all the data that could potentially have issues?

 

Or an easier way to do it would be fine too =)

 

(This may belong in mysql forums, sorry for posting it in php)

Thanks,

Justin

[mrMarcus]

using array_map() can be helpful:

 

$_POST = array_map ('mysql_real_escape_string', $_POST);

 

will run mysql_real_escape_string() against all incoming $_POST variables automatically.

[PFMaBiSmAd]

I'm guessing that the data path from draft to final table (you should actually just use your final table but with a status column that indicates it is a draft and then just change the status to 'final' when you are done) does not pass through $_POST variables.

 

Short answer - all string data put into any query, no matter where that data comes from, must be escaped if it could contain any special sql characters.

[Mchl]

 

Is there an easy way to use an equivalent function of mysql_real_escape_string in a query to escape all the fields

 

That just wouldn't make sense, because you're escaping the data, so that it doesn't break your query.

 

You can use prepared statements, which theoretically give you 100% protection against SQL injection (if used properly), but they're often a bit cumbersome to use.

[The14thGOD]

This is the first time I had to add a preview function to my CMS so I went with what I thought would be easier to maintain. This is really the only hiccup I've run into because of the 2 tables.

 

You would be correct, I am just grabbing the data from a query and using that data in another update. No $_POST at all.

 

I'll go ahead and add hte mysql_real_escape_string function then to those fields. Luckily its a pretty small database.

 

Thanks,

Justin

[Mchl]

You can just do all this in query without passig the data through PHP

 

 

INSERT INTO table1(field1, field2, ...) SELECT (field1, field2,... ) FROM table2 WHERE ...

[The14thGOD]

You can just do all this in query without passig the data through PHP

 

 

INSERT INTO table1(field1, field2, ...) SELECT (field1, field2,... ) FROM table2 WHERE ...

 

I woudln't have to worry about data being escaped then?

 

(apparently mysql_real_escape_string() is not solving my issue. It's not liking the single quotes/apostrophes ', in the data. Any ideas?)

 

It also may be helpful to note, the field im having issues with is a tinyMCE field.

[PFMaBiSmAd]

(apparently mysql_real_escape_string() is not solving my issue. It's not liking the single quotes/apostrophes ', in the data. Any ideas?)

Cannot specifically help with the code producing that symptom without seeing the code. Best guess is that you did not assign the output from the msyql_real_escape_string() function call to the variables being used in the query.

[The14thGOD]

<?php
$preview_query = "SELECT * FROM preview WHERE uid='$_GET[uid]' ";
$query2 = "UPDATE website SET pageid='$preview_row[pageid]',navtitle='".mysql_real_escape_string($preview_row['navtitle'])."',title='".mysql_real_escape_string($preview_row['title'])."',keywords='".mysql_real_escape_string($preview_row['keywords'])."',description='".mysql_real_escape_string($preview_row['description'])."',url='$preview_row[url]',headline='".mysql_real_escape_string($preview_row['headline'])."',body='".mysql_real_escape_string($preview_row['body'])."',status='enabled' WHERE uid='$_GET[uid]' ";
?>

[Mchl]

$updateQuery = 'UPDATE website AS w INNER JOIN preview AS p ON (w.uid = p.uid) SET w.pageid = p.pageID, w.navtitle=p.navtitle, w.title=p.title, w.keywords=p.keywords, ... WHERE w.uid = '.mysql_real_escape_string($_GET['uid']);

[The14thGOD]

I was just talking with someone about joins, guess this will be the first time I've used one. Thanks, I will try this and let you know!

 

Justin

[The14thGOD]

The single quote is still not coming through to the website table. It's in the draft table (I'm in the database looking at it). It previews just fine (single quote is there in the copy) but after it publishes it still breaks. None of the changes I do happens (for example I just deleted a line of text and added single quotes, when I go to update, the text is the same as it was before I removed it. Meaning the query failed, but I'm not getting an error.)

[Mchl]

Since there's no actual data in the query (apart from uid), it cannot make query fail. The problem must be somewhere else.

[The14thGOD]

Ya I'm stumped. i just did the query from SQL and it ran fine. Copied everything over.

I do it from the site and it doesn't work.

 

Here's the entire script:

<?php
import_request_variables('pg');	
include('includes/connect_db.php');
include('includes/urlpathback.php');
include('includes/adminloggedin.php');
//Grab Info from databases
$website_query = "SELECT * FROM website WHERE uid='$_GET[uid]' ";//$_SESSION['theurl'] is what the old url used to be from edit.php
$website_result = mysql_query($website_query);
$website_row = mysql_fetch_assoc($website_result);
$preview_query = "SELECT * FROM preview WHERE uid='$_GET[uid]' ";//$_SESSION the new url which is stored in preview's db
$preview_result = mysql_query($preview_query);
$preview_row = mysql_fetch_assoc($preview_result);

//upate database
$query = "UPDATE subnav SET parent='$_SESSION[parent]',navtitle='$preview_row[navtitle]',url='$preview_row[url]',weight='$_SESSION[weight]',status='$_SESSION[status]' WHERE uid='$_GET[uid]' "; //Since subnav wasn't updated, you use the variable made in edit.php
mysql_query($query);
//$query2 = "UPDATE website SET pageid='$preview_row[pageid]',navtitle='$preview_row[navtitle]',title='$preview_row[title]',keywords='$preview_row[keywords]',description='$preview_row[description]',url='$preview_row[url]',headline='$preview_row[headline]',body='$preview_row[body]',status='enabled' WHERE uid='$_GET[uid]' ";
//mysql_query($query2);

$updateQuery = 'UPDATE website AS w INNER JOIN preview AS p ON (w.uid = p.uid) SET w.navtitle=p.navtitle, w.title=p.title, w.keywords=p.keywords, w.description=p.description, w.url=p.url, w.headline=p.headline, w.body=p.body, w.status=p.status WHERE w.uid = '.mysql_real_escape_string($_GET['uid']);
mysql_query($updateQuery);

//change all items that have a parent of as this page
//if($oldnavtitle != strtolower(preg_replace('/[^a-zA-Z0-9-]/','',str_replace(' ','-',$navtitle)))) {
//$newurl = str_replace($oldnavtitle,$url,$something);
if($_SESSION['oldparent'] != $_SESSION['parent'] || $_SESSION['oldnavtitle'] != strtolower(preg_replace('/[^a-zA-Z0-9-]/','',str_replace(' ','-',$preview_row['navtitle'])))){	
	$query = "SELECT * FROM subnav WHERE parent='".$_SESSION['oldparent'].'/'.strtolower(preg_replace('/[^a-zA-Z0-9-]/','',str_replace(' ','-',$_SESSION['oldnavtitle'])))."' ";
	$result = mysql_query($query);
	while($row = mysql_fetch_assoc($result)){
		$query2 = "UPDATE subnav SET parent='$preview_row[url]', url='$preview_row[url]".'/'.strtolower(preg_replace('/[^a-zA-Z0-9-]/','',str_replace(' ','-',$row['navtitle'])))."' WHERE uid='$row[uid]' ";
		mysql_query($query2);
		$query3 = "UPDATE website SET pageid='$preview_row[pageid]', url='$preview_row[url]".'/'.strtolower(preg_replace('/[^a-zA-Z0-9-]/','',str_replace(' ','-',$row['navtitle'])))."' WHERE uid='$row[uid]' ";
		mysql_query($query3);
	}
}
//done updating rest of pages

unset($_SESSION['editdraft'],$_SESSION['oldnavtitle'],$_SESSION['oldparent'],$_SESSION['status'],$_SESSION['weight'],$_SESSION['parent'],$_SESSION['theurl'],$_SESSION['draft'],$_SESSION['uid'],$_SESSION['editurl']);
header("Location: $preview_row[url]");
exit(0);
?>

 

I mean really its just those 2 lines we are concerned about, the other stuff is editing other crap. I don't know what else could be affecting it, like I said, the data gets into the draft table just fine.

 

Justin

[The14thGOD]

ok, hold on I think I figured it out, and it's a dumb thing... on my end of course =P

 

Sigh, brb, to attempt this...

 

*Edit *

Yep I'm an idiot. I built this thing like 2-3 months ago and forgot that the main navigation pages are not editable, navigation wise, so they are on a slightly different (easier) system to modify the pages. I was on the completely dynamic pages system. So the reason why I wasn't seeing anything was because that page wasn't being called at all. I updated info and copied the INNER JOIN SQL and it worked fine. Thank you and sorry for all the posts.