NYCPHP Meetup

[nycphp-talk] Is there something wrong with this SQL query in PHP?

Brian Dailey support at dailytechnology.net
Tue Aug 14 15:52:34 EDT 2007


I would take Brian O'Connor's suggestion, first... make sure you're 
getting the expected values in $_POST. Use var_dump($_POST) to get all 
of the values.

Also, I don't know if you're already checking this or not, but given the 
SQL statement you provided your application is definitely open for SQL 
injection. If I post a value where id = [1=1;DROP TABLE jobsdb] I could 
create some massive headaches for you. Make sure you're clearly defining 
datavalues by using either prepared statements or some sort of 
mysql_real_escape function (wrapping each value in slashes in the query 
itself, too, even if it's an integer).

- Brian D.

Anthony Wlodarski wrote:
> I have been staring at this for about a week and it is turning into a 
> mind bender.  Here is the code excerpt:
> 
>  
> 
> // pre: none
> 
> // post: displays our controls for …
> 
> function displayControls()
> 
> {
> 
>         if($_POST['editjob'])
> 
>         {
> 
>                 $query = "SELECT * FROM `jobsdb` WHERE `id` 
> =".$_POST['id']."”;
> 
>                 $result = mysql_query($query) or die("Error editing data.");
> 
>                 $row = mysql_fetch_assoc($result, MYSQL_ASSOC);
> 
>  
> 
>                 echo "Debug: ".$_POST['id']." - ".$row['id']."\n";
> 
>  
> 
>                 // display all our form data
> 
>                 echo "<fieldset style='color: #ffffff;'><legend>Edit a 
> job</legend>\n".
> 
>                                 "<form action='".$_SERVER['PHP_SELF']."' 
> method='post'>\n".
> 
>                                 "Recruiter: <input name='recruiter' 
> type='text' value='".$row['recruiter']."'></br>".
> 
>                                 " Job ID: <input name='jobid' 
> type='text' value='".$row['jobid']."'></br>".
> 
>                                 " Position: <input name='position' 
> type='text' value='".$row['position']."'></br>".
> 
>                                 " Position Link: <input name='link' 
> type='text' value='".$row['link']."'></br>".
> 
>                                 " Additional Info: <input 
> name='additionalinfo' type='text' value='".$row['additionalinfo']."'></br>".
> 
>                                 " Salary: <input name='salary' 
> type='text' value='".$row['salary']."'></br>".
> 
>                                 " Hiring Manager: <input 
> name='hiringmanager' type='text' value='".$row['hiringmanager']."''></br>".
> 
>                                 " Company: <input name='company' 
> type='text' value='".$row['company']."'></br>".
> 
>                                 " Status: <input name='status' 
> type='text' value='".$row['status']."'></br>".
> 
>                                 " Postings: <input name='postings' 
> type='text' value='".$row['postings']."'></br>".
> 
>                                 " Job Color: \n".
> 
>                                 "<select name='color'>\n".
> 
>                                 "<option value='#ff0000'>Red</option>\n".
> 
>                                 "<option value='#ffa500'>Orange</option>\n".
> 
>                                 "<option value='#ffff00'>Yellow</option>\n".
> 
>                                 "<option value='#00ff7f'>Spring 
> Green</option>\n".
> 
>                                 "<option value='#32cd32'>Lime 
> Green</option>\n".
> 
>                                 "<option value='#c0c0c0'>Silver</option>\n".
> 
>                                 "<option value='#0000cd'>Medium 
> Blue</option>\n".
> 
>                                 "<option value='#191970'>Midnight 
> Blue</option>\n".
> 
>                                 "<option value='#4169e1'>Royal 
> Blue</option>\n".
> 
>                                 "<option value='#c71585'>Medium Violet 
> Red</option>\n".
> 
>                                 "<option 
> value='#ff00ff'>Fuchsia</option>\n".
> 
>                                 "</select>\n".
> 
>                                 "<input name='saveedit' type='submit' 
> value='Save Edit'>".
> 
>                                 "<input name='cancel' type='submit' 
> value='Cancel'>".
> 
>                                 "</form>".
> 
>                         "</fieldset>\n";
> 
>         }
> 
>  
> 
> ……. Rest of application.
> 
>  
> 
> Now the big problem are the three PHP statements after the “if” 
> statement.  When I click edit job nothing appears even in the debug 
> statement, I am not returning a valid id from $row[‘id’] nor does any of 
> the data appear in the form(which is just an integer).  I have 
> scrutinized the SQL query a million times, even tried to qualify it with 
> the database name using `foobar`.`jobsdb` and that still not do 
> anything.  I know the $_POST[‘id’] variable is passing the right id 
> based on the echo command.  Is this a SQL issue or a PHP issue?  I have 
> looked at my databases in phpMyAdmin and even checked them using the 
> command line on our box and they have the right permissions for the 
> users that can access them.  If this is  a SQL issue can you guys point 
> me in the right direction?  I was thinking of pulling my data base 
> handle code and the SQL queries and just throwing them into a bare 
> script to see what happens.  If anyone needs to see the HTML output I 
> can get that as well.
> 
>  
> 
> Any help is appreciated.
> 
>  
> 
> Thanks,
> 
>  
> 
> /Anthony/
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> New York PHP Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
> 
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
> 
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php

-- 

Thanks!
- Brian Dailey
Software Developer
New York, NY
www.dailytechnology.net
-------------- next part --------------
A non-text attachment was scrubbed...
Name: support.vcf
Type: text/x-vcard
Size: 264 bytes
Desc: not available
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20070814/e1da1bc9/attachment.vcf>


More information about the talk mailing list