PHP & SQL help

[Rat]

Once again, after several years, I'm trying to get into PHP/MySQL website building. Turns out I'm still not very good at it. I have a specific problem that I'm hoping someone can point me in the right direction for.

I have a page that contains half a dozen text and textarea inputs for updating the information in a specific table. If I update a column that is a varchar type, it works fine, but if I try it with any other type, it fails, even when it's a text field. There are no errors, as far as I can tell; it just doesn't update.

Do I have to do anything different when updating text fields, or am I condemned to going through the source for the 438th time looking for where I might have missed a semicolon or closing bracket?

 

[Octavo]

Once again, after several years, I'm trying to get into PHP/MySQL website building. Turns out I'm still not very good at it. I have a specific problem that I'm hoping someone can point me in the right direction for.

I have a page that contains half a dozen text and textarea inputs for updating the information in a specific table. If I update a column that is a varchar type, it works fine, but if I try it with any other type, it fails, even when it's a text field. There are no errors, as far as I can tell; it just doesn't update.

Do I have to do anything different when updating text fields, or am I condemned to going through the source for the 438th time looking for where I might have missed a semicolon or closing bracket?

I'm not a PHP/MySQL expert, but I've done a fair bit of dev in that area. It's a tad difficult to assist without seeing the code though. Perhaps just the lines where you open the sql connection and pass through the insert/update?

 

[oody]

I find that when debugging such things, it helps to echo the assembled SQL command to the screen, cut it, and paste it into the SQL interpreter window of PhpMyAdmin to see if your command has half a chance of doing what you want it to do, outside the other code. This may also reveal that you're not getting what you think you're getting from the form.

 

[Octavo]

I find that when debugging such things, it helps to echo the assembled SQL command to the screen, cut it, and paste it into the SQL interpreter window of PhpMyAdmin to see if your command has half a chance of doing what you want it to do, outside the other code. This may also reveal that you're not getting what you think you're getting from the form.

Yes, a most useful tip and something I used to do a bunch. In the javascript world, the equivalent is alert(); - I remember one troublesome piece of script that I eventually had an alert after each line with numbers so I could see exactly where things went down.

There may be easier ways, but that's the fastest for me

 

[Rat]

I'm not a PHP/MySQL expert, but I've done a fair bit of dev in that area. It's a tad difficult to assist without seeing the code though. Perhaps just the lines where you open the sql connection and pass through the insert/update?

The connection must be opened correctly, I think, because the varchar fields do get updated. The update line is this:

mysql_query("UPDATE pubs_pubs SET pubname='$pubname', xcoord='$xcoord', ycoord='$ycoord', locdesc='$locdesc', zoneref='$zoneref', flagcol='$flagcol' WHERE id='$id'")
 or die(mysql_error()); 
 
 echo 'done!'

In this example, if I edit all fields, pubname and locdesc, which are varchars, will update fine, while notes, which is text, will remain unchanged.

I suppose I could in theory just change notes to be a very long varchar, since I'm on MySQL 5.1, but as this is largely a learning exercise, I'd like to know why it isn't working now.

 

[Donn]

Dump all your vars - never assume they hold what you suppose.
Dump the mysql return values/errors. I forget the details.
Switch to Wordpress.
Switch to Python and Django.
Quit web dev and let youngsters do it for you.

 

[icerat]

I don't usually code PHP, but I would have thought it should be more like this -

mysql_query("UPDATE pubs_pubs SET pubname='".$pubname."', xcoord='".$xcoord."', ycoord='".$ycoord."', locdesc='".$locdesc."', zoneref='".$zoneref."', flagcol='".$flagcol."' WHERE id='".$id."'")
 or die(mysql_error()); 
 
 echo 'done!'

 

[icerat]

I don't usually code PHP, but I would have thought it should be more like this -

mysql_query("UPDATE pubs_pubs SET pubname='".$pubname."', xcoord='".$xcoord."', ycoord='".$ycoord."', locdesc='".$locdesc."', zoneref='".$zoneref."', flagcol='".$flagcol."' WHERE id='".$id."'")
 or die(mysql_error()); 
 
 echo 'done!'

nah, apparently you don't need to do that in php.

 

[Rat]

The "echo 'done!'" is just there, by the way, because at first I thought it was not editing any fields, so I wanted to check it was reading through the line in question. It's less relevant now that I know I can edit the varchar fields.

 

[Octavo]

First of all, there doesn't appear to be a "notes" field in your insert statement - unless I'm going blind.

I'd recommend you escape the Notes variable before trying to insert. If you're getting the text out of a TextArea, there may well be formatting or other values in there which are borking the insert:

$text = mysql_real_escape_string($_POST['Notes']);

 

[Donn]

Another trick: make a string of the query and print it.

$q = "UPDATE pubs_pubs SET pubname='$pubname', xcoord='$xcoord', ycoord='$ycoord', locdesc='$locdesc', zoneref='$zoneref', flagcol='$flagcol' WHERE id='$id'";
echo $q;

Then make sure it follows the rules.

 

[Rat]

A ha ha ha ha. Yes, it was not updating that field because I didn't tell it to, and I didn't notice this because I am a dick. Thanks for that.

Furthermore, I spent a long time today in MSSQL trying to edit a view, and flummoxed as to why it wasn't working. It wasn't working because, of course, the command is 'alter', not 'update'.

I really should give up, but I'm going to get this finished if it kills me.

The inputs are all escaped, by the way, along the lines of $pubname = mysql_real_escape_string(htmlspecialchars($_POST['pubname']));, before they are then sent to the DB. I hope that is sufficient to strip out anything untoward.

 

[Donn]

I hope that is sufficient to strip out anything untoward.

In no way will this be sufficient. This is one of the reasons to use a framework (like CakePHP, or Wordpress) where they have put serious man-hours into solving security leaks.

Stopping the gaps on your own is a good exercise, but you will not get far. Have a gander:

http://en.wikipedia.org/wiki/Web_application_security

 

[Rat]

Well, yes and no. The site I'm attempting to build will be in no way a large commercial site. I do have some experience of managing sites based on CMFs (mostly the at least formerly execrable Joomla), so I know that they bring many problems of their own. Particularly, any of the CMFs that I know do far far more than I will ever need for this site.

Most of the problems listed on that Wiki page are not really applicable in this case, so it's mostly SQL injections that I need to be aware of, especially as I have little control over the web server or SQL engine, which are supplied by the host. There needs to be a tiny bit of communication between site and database, but otherwise it's mostly a static site.

Since it's relatively small and noncommercial, if I make a stupid mistake that allows script kiddies to bork things, I can just restore the most recent database dump, take the site offline for a day or two to figure out how it was done, and then fix it. Which is a lot easier if it's a database of half a dozen tables that I made myself, rather than the massive multitude of tables and hundreds of pages of code that come with a CMF.

tl;dr: I'm generally aware of the dangers, but they're not really applicable here.

 

[xterra]

Two off-topic comments:

1. You are not a dick, you are a Rat.

2. I am pleased not to be the only one who does not like Joomla. I volunteered as a tech person at a museum for a few years, and I hated Joomla so much that I recruited someone else to be the webmaster.

3. I am always interested in how people approach problems like this.


ETA. Did you actually want me to count?

 

[Octavo]

Two off-topic comments:

1. You are not a dick, you are a Rat.

2. I am pleased not to be the only one who does not like Joomla. I volunteered as a tech person at a museum for a few years, and I hated Joomla so much that I recruited someone else to be the webmaster.

3. I am always interested in how people approach problems like this.


ETA. Did you actually want me to count?

Another Joomla hater here. I used to do a bit of freelance web-design work and on occasion set up a Joomla based site for a client. It's awful.

 

[Donn]

Cool, I was just being overly nitpickety. Your project seems under control of its scope.

FWIW, I look at the security problem this way: I'm too stupid to assume I can sanitize strings.

I've not touched Joomla, other than a day of testing, but I've a similar aversion to Wordpress, oddly — I have recommended it because it does work well, it just takes a lot of patience to bend your will to its Way Of Things.

After a few years of PHP, I took a wild stab into Python. Django was a real pleasure, but over time web work has fallen away.

 

[PixyMisa]

Rat, are you open to using a different language? I wouldn't recommend using PHP for anything under any circumstances. Python and Ruby are much better choices.

 

[Childlike Empress]

Rat, are you open to using a different language? I wouldn't recommend using PHP for anything under any circumstances. Python and Ruby are much better choices.

If your buddies ask you to throw together some site for pub enthusiasts and you think spending time with them in one of the venues that database thing shall contain is much more fun than reading "well-meaning" pedantic tips of people programming for different goals, there's nothing better than PHP. That's what it was invented for, after all.

 

[Donn]

The Empress makes an undeniable point. Carry on.