Adding raw variable to mysql database

Forum Moderators: coopster

Message Too Old, No Replies

Adding raw variable to mysql database

rs7272

7:10 pm on Jun 29, 2010 (gmt 0)

I have a form adding info to a database. All works until I try to add a variable.

I want the value in the table to be "blah blah blah $variable blah blah blah" so that when that info is eventually displayed it'll replace "$variable" with the appropriate value.

Everything works other than getting "$variable" added to the table.

Thanks in advance and hope that made sense...

Matthew1980

7:18 pm on Jun 29, 2010 (gmt 0)

Hi there rs7272,

Welcome to webmasterworld ;) [webmasterworld.com ]

Could you post a portion of the code you are having issues with, then from that we can try to help you out.

Cheers,
MRb

rs7272

7:30 pm on Jun 29, 2010 (gmt 0)

Here's the form and insert code. Everything works aslong as I don't put any variables in the inputs. I assume there's a way to escape the $ (or something similar) to get it added to the table. If I put a $ in any input field, nothing gets added at all.

Thanks


<?php

if ($_POST['action'] == "add"){

$Headline = $_POST['Headline'];
$Body = $_POST['Body'];
$Closing = $_POST['Closing'];

$con = mysql_connect("localhost", "database", "password");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("database", $con);

mysql_query("INSERT INTO PT_Articles (Headline, Body, Closing)
VALUES ($Headline, $Body, $Closing)");

mysql_close($con);
}
?>
<form method=post action=addarticle.php>
<input type=text name=Headline><br>
<textarea name=Body></textarea><br>
<textarea name=Closing></textarea><br>
<input type=hidden name=action value=add>
<input type=submit>
</form>

rs7272

7:50 pm on Jun 29, 2010 (gmt 0)

also, there are no error messages - if I put a variable in any text field the form submits fine, but nothing ends up in the database...

Matthew1980

7:54 pm on Jun 29, 2010 (gmt 0)

Hi there rs7272,

I have partly re-written this for you, added some security & better error handling. Typed on the fly but it should do the job ;) Commented where I needed to for clarity

<?php
//check to see if form isset & holds value
if(isset($_POST['submit']) && ($_POST['submit'] == "add")){

//assign vars & cleanse input (always sanitise user input)
$Headline = strip_tags($_POST['Headline']);
$formBody = strip_tags($_POST['Body']);//changed var name as it *might be a reserved word*
$Closing = strip_tags($_POST['Closing']);

//establish connection once working, remove the error handler just leave as .... "password"); etc ;)
$con = mysql_connect("localhost", "database", "password") or die(mysql_error());

//select db
mysql_select_db("database", $con);

//perform query & add vars into query
$sqlQuery = "INSERT INTO `PT_Articles` ( `Headline`, `Body`, `Closing`) VALUES ('".$Headline."', '".$formBody."', '".$Closing."') LIMIT 1";

//run query & if successful display message
if(mysql_query($sqlQuery)){
//echo success message here
echo "Successfully added into DB";
}else{
//Error occured in adding to DB
echo "error has occured";
}

//close connection
mysql_close($con);

//always good to handle a mis-set form submission
}else{
//redirect back to form as error has occurred
header("location: NAME_OF_THIS_FILE_HERE.php");
}
//lastly display original page, the insert should be successful IF it was actioned
?>
<html>
<head>
<title>Adding value to DB
</title>
</head>
<body>
<form method="post" action="addarticle.php">
<input type="text" name="Headline" /><br>
<textarea name="Body"></textarea><br>
<textarea name="Closing"></textarea><br>
<input type="submit" name="submit" value="add" />
</form>
</body>
</html>

Not sure whether you are fussed about being W3C compliant but, its worth putting double quotes around the attributes and their values, makes for better coding IMHO.

[EDIT]:
There may be no error messages, but do you have them turned on by default? if not just do this as the first line of code in the script:-

<?php
//error reporting level
error_reporting(E_ALL);//This flags up everything, remove this line when going live though :)

Hope it helps you,

Cheers,
MRb

[edited by: Matthew1980 at 8:12 pm (utc) on Jun 29, 2010]

rs7272

8:02 pm on Jun 29, 2010 (gmt 0)

Thanks for your help - I'll try this out and cross my fingers :) This is a small internal app, so I wasn't concerned with compliance or security - quick and dirty...

Thanks again

Matthew1980

8:05 pm on Jun 29, 2010 (gmt 0)

Hi there rs7272,

just thought I should ask ;)

No problem, just make sure you put the correct filename in the header() function where I put capital letters, or else it WILL give an error.

Cheers,
MRb

rocknbil

1:42 am on Jun 30, 2010 (gmt 0)

A simplified (though less robust) answer is the cause of your problem is your insert values aren't quoted.

mysql_query("INSERT INTO PT_Articles (Headline, Body, Closing)
VALUES ('$Headline', '$Body', '$Closing')");

Not sure why M is putting a limit clause on an insert?

But I'm not sure what this means:

so that when that info is eventually displayed it'll replace "$variable" with the appropriate value.

You mean . . . you want to store some "token" in your database you'll swap out later? Like

$value = 'some value [TOKEN] here';
$swap = 'goes';

Then store $value in database, and when it's read later, put $swap where [TOKEN] is

$value = str_replace('[TOKEN]',$swap);

so it reads

some value goes here

If so, the previous kinda' demonstrates how you'd do that.

amoore

2:10 pm on Jun 30, 2010 (gmt 0)

You might want to use mysql placeholders and bind your variables to them. This can help reduce the likelyhood of an SQL injection attack. Consider what happens when someone fills in the form with the "headline" set to something like:

'; DELETE FROM PT_Articles;

Since you're letting PHP do the variable substitution, that SQL that was supplied by the user will get executed by your database.

Matthew1980

5:46 pm on Jun 30, 2010 (gmt 0)

Hi all,

Rocknbil,
>>Not sure why M is putting a limit clause on an insert?

Force of habit methinks! I doubt as it will matter though as by nature of INSERT it will only insert 1 record set at a time, unless you are in a loop ;)

amoore,
>>You might want to use mysql placeholders and bind your variables to them. This can help reduce the likelyhood of an SQL injection attack.

That is precisely why I put the $_POST vars through strip_tags, admittedly I could use mysql_real_escape_string() too, but for the exercise of getting the sql working I thought as strip_tags() was ample.

After reading Rocknbils reply, it makes sense as to what he is asking, is this what you were explaining?

Cheers,
MRb