Inserting values into multiple MySQL tables at once

Question

I've created mini content management system. Now got afew questions

I'm filtering posts with following function

function filter($data, $db)
{
    $data = trim(htmlentities(strip_tags($data)));
    if (get_magic_quotes_gpc())
    $data = stripslashes($data);
    $data = $db->escape_string($data);
    return $data;
}

And the PHP code looks like that

$name=filter($_POST['name'], $db);
$title=filter($_POST['title'], $db);
$parent=filter($_POST['parent'],$db);
$switch=filter($_POST['switch'], $db);
    if($switch=''){
        echo "Return back and select an option";
        die();
    }
$parentcheck=filter($_POST['parentcheck'],$db);
    if($parentcheck=='0')
    {
        $parent=$parentcheck;
    }   
$purifier = new HTMLPurifier();
$content = $db->real_escape_string( $purifier->purify( $_POST['content']) );

if(isset($_POST['submit'])&&$_POST['submit']=='Ok'){
    $result=$db->query("INSERT INTO menu (parent, name, showinmenu) VALUES ('$parent', '$name', '$switch'") or die($db->error);
    $result2=$db->query("INSERT INTO pages (id, title, content) VALUES ('<what?>', '$title', '$content'") or die($db->error);           
    }

And that's how my tables look like

Table named "pages" enter image description here

And "menu"

enter image description here

My questions are followings:

I'm trying to get autoincremented id value from menu table after ('$parent', '$name', '$switch'") insertion and set this id in pages table while inserting ($title, $content). How to do it? Is it possible with single query?
$content's value is the text with HTML tags. I'm using html purifier. May I filter it's value too before inserting into db table? Any suggestion/advice?

Are you looking for mysql_insert_id() function?

Dumbo
– Dumbo

2011-09-30 20:13:06 +00:00
Commented Sep 30, 2011 at 20:13 — Dumbo
– Dumbo, Commented Sep 30, 2011 at 20:13

Dominic · Accepted Answer · 2011-09-30 20:13:44Z

1

Should be

$result2=$db->query("INSERT INTO pages (id, title, content) VALUES (LAST_INSERT_ID(), '$title', '$content'") or die($db->error);

Filtering using real_escape_string( ) should be safe. Is there something else that you want to filter?

answered Sep 30, 2011 at 20:13

Dominic

8068 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc B · Accepted Answer · 2011-09-30 20:13:56Z

1

Looks like you're using mysqli as the DB library, so you can use $db->insert_id() to retrieve the LAST id created by an insert operation by that particular DB handle. So your queries would become:

$result=$db->query("INSERT INTO menu (parent, name, showinmenu) VALUES ('$parent', '$name', '$switch'") or die($db->error);
$new_id = $db->insert_id();
$result2=$db->query("INSERT INTO pages (id, title, content) VALUES ($new_id, '$title', '$content'") or die($db->error);           
                                                                    ^^^^^^^

You can't really do it in a single query, as mysql does not make the ID value available for the insert_id function until AFTER the query completes. So you do have to do this in a 3 step process: insert, get id, insert again.

The rule for DB filtering (better known as escaping) is to escape ANYTHING that's user-provided. This even includes data you've retrieve in other db queries and are re-inserting. Escaping isn't really there as a security measure - it's there to make sure that whatever you're putting into the query string doesn't BREAK the query. Preventing SQL injection attacks is just a side effect of this.

answered Sep 30, 2011 at 20:13

Marc B

362k44 gold badges433 silver badges508 bronze badges

6 Comments

demonoid Over a year ago

Did you read the code of my filter function. $content's value is the text with HTML tags. I wanna filter $content too. Any advice about inserting html markup into db?

Marc B Over a year ago

Purifier has nothing do with inserting html into a database. purifier just cleans up the html. How you want to store the data and what you're going to use it for afterwards is up to you. but purifier by itself is NOT enough to make some arbitrary html safe for database use. They're two completely different usage situations. Comparing apples and giraffes.

demonoid Over a year ago

What you'd advice to do before inserting html into db table?

Marc B Over a year ago

Try it like any other string data. Just because it's got some tags in it doesn't make it non-string. Though you'd want to rethink your filter function, since it destroys html/xml. Or have one specifically for html/xml.

demonoid Over a year ago

can you help me to write one for html?

|

Collectives™ on Stack Overflow

Inserting values into multiple MySQL tables at once

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related