1

Re: PHP and MySQL

Does anyone here know how to transfer data between an html form and a mysql database.  I have the code mostly operational, but I'm having trouble dealing with special characters such as apostrophes.  Every code solution I've seen online seems to fail.  Could someone advise?

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

2

Re: PHP and MySQL

Hi Steve

It sounds like the data needs  "escaping"  before inserting into the database. Have a look at;

http://php.net/manual/en/function.mysql-real-escape-string.php

or this;

http://php.net/manual/en/function.addslashes.php

The apostrophe is one such character that needs to be treated before being saved to a SQL database. Escaping is the method of adding characters (e.g. '\') before any of the characters which have special meaning to the underlying software.

Hope this helps.

Best Wishes, David.

Digital Sankhara - Crafted Software.

3 (edited by mbeard 01-04-2011 12:56:02)

Re: PHP and MySQL

Hi Steve,

Another important point regarding escaping is that it prevents hackers from performing SQL Injection attacks.  http://php.net/manual/en/security.database.sql-injection.php

Looks like David's first suggestion is an important component towards preventing this: "This function must always (with few exceptions) be used to make data safe before sending a query to MySQL."

If the code that you have found is not dealing with this I wonder what other holes are being left open?  It is several years since I was a small time web developer and things have clearly moved on.  These days you seem to need captcha systems for anything, annoying as they are.   http://en.wikipedia.org/wiki/CAPTCHA

It might be best to see which standard pre-written forms your ISP provides as part of their web hosting service.  And do a google on the name of these standard forms to see if they are considered secure.  It might be worth using a third party form filling website: a quick google produced these guys, but I have not googled to see their reputation and of course you want to link to your database but you get the idea... http://www.emailmeform.com/

As a matter of interest which programming language are you using? [edit: doh! Note to self: read the topic title!!]

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

4

Re: PHP and MySQL

This is the code I've written to try and pass the data in using PHP.  However, the code fails and for the life of me I cannot figure out what is wrong with it.  I'm new at this, and while I understand what needs to be done, everything I try to actually do to make it work doesn't.

$name = $_POST["txtName"];
$email = $_POST["txtEmail"];
$address = $_POST["txtAddress"];
$total = $_POST["txtTotal"];
$comments = $_POST["txtComments"];
$location = $_POST["txtLocation"];
$GR = $_POST["txtGR"];

$con = mysql_connect("localhost","username","password");
mysql_select_db("databasename", $con);

$name = mysql_real_escape_string($name, $con);
$email = mysql_real_escape_string($email, $con);
$address = mysql_real_escape_string($address, $con);
$total = mysql_real_escape_string($total, $con);
$comments = mysql_real_escape_string($comments, $con);
$location = mysql_real_escape_string($location, $con);
$GR = mysql_real_escape_string($GR, $con);



$sql="INSERT INTO tablename (Name, Email, Address, Total, DORA, Comments, Location, GR, Day, Month, Year)
VALUES
('$name','$address','$total','$_POST[radDORA]','$comments','$location','$GR','$_POST[selDay]','$_POST[selMonth]', '$_POST[selYear]')";

$mysql_query =($sql, $conn);

The original code, that does pass the data in, but doesn't have an escape string reads like this:

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


mysql_select_db("databasename", $con);

$sql="INSERT INTO tablename (Name, Email, Address, Total, DORA, Comments, Location, GR, Day, Month, Year)
VALUES
('$_POST[txtName]','$_POST[txtEmail]','$_POST[txtAddress]','$_POST[txtTotal]','$_POST[radDORA]','$_POST[txtComments]','$_POST[txtLocation]','$_POST[txtGR]','$_POST[selDay]','$_POST[selMonth]', '$_POST[selYear]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

mysql_close($con)

if anyone can help i'd be very appreciative.

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

5

Re: PHP and MySQL

Ignore that smiley, it should read $mysql_query: ($sql, $con);

Also, i know there  is a spelling mistake in the $con in the top post (it reads $conn in one section) but this isn't the problem i'm having.

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

6

Re: PHP and MySQL

Steve,

I'm no expert with PHP, but I note that you are using string expansion in the SQL query. Check to see if the code needs to sit within double quotes instead of single ones.

"$name" as opposed to '$name'

Also, as your SQL construction string is already within double quotes, then maybe you do not need quotes around an already quoted string held in your variables. Just a thought. I may have some time later to try and run your code myself.


Hope this helps.

Best Wishes, David.

Digital Sankhara - Crafted Software.

7

Re: PHP and MySQL

I found this in the mysql_real_escape_string manual page, which might be relevant?

"If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice."

Otherwise perhaps you need to turn on debugging mode in PHP so that we can get a better idea of what exactly is happening or not happening?  This seems like a reasonable guide http://thinkvitamin.com/code/how-to-debug-in-php/

Mike Beard
Natural Course Project Officer
Greater Manchester Local Records Centre

8

Re: PHP and MySQL

Problem solved!  The " marks in the initial $_POST were the problem, shouldn't have been there.  All is well now.  Thanks for all your help.

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre