problems with "" in database

[digitalgod]

hey guys,

I made a news script where I can write stuff and it will get stored in a database, my only problem is when I try to retrieve it or query the table it won't work properly if I have " " or ' ' in the entry.

Here's an example:

I have this form:

[code]<div id="article">
<form action="admin.php?a=editevent" method="post">
<input type="hidden" name="process" value="yes" />
<table border="0">
<tr>
        <td>Event:</td>
        <td><select name="ename" id="ename">
            <option>Choose one</option>
            <?php
$sql='SELECT * FROM '.$prefix.'events';
$req=mysql_query($sql) or die(query_error());
while($data = mysql_fetch_assoc($req)) {
    echo '<option value="'.$data['name'].'">'.$data['name'].'</option>';
}
?>
          </select></td>
</tr>
</table>
<br />
<input type="submit" value="Edit Event" />
</form>
</div>[/code]

and one of the events is named A-TRAK "SUNGLASSES IS A MUST TOUR"

so when I do this query

$name=$_POST['ename'];
$result=mysql_query("SELECT * FROM ".$prefix."events WHERE name='$name'") or die(query_error());
$row=mysql_fetch_array($result);

it tells me that the event doesn't exist because all it returns is A-TRAK

any way I can fix this?

[Yojance]

Join the club, I get a lot of errors and dont know how to properly extract information from a database.
Im sure you will find some help here.

[.josh]

don't even know what you are trying to do here:

$sql='SELECT * FROM '.$prefix.'events';

is $prefix your table name, and is events supposed to be the condition you are wanting to base the selection on? like,
[code]
$prefix = 'tablename';
$events = 'blah';

$sql = "select * from '$prefix' where events = '$events'";
[/code]
?

post what you are trying to do

[digitalgod]

[!--quoteo(post=376897:date=May 25 2006, 12:51 AM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ May 25 2006, 12:51 AM) [snapback]376897[/snapback][/div][div class=\'quotemain\'][!--quotec--]
don't even know what you are trying to do here:

$sql='SELECT * FROM '.$prefix.'events';

is $prefix your table name, and is events supposed to be the condition you are wanting to base the selection on? like,
[code]
$prefix = 'tablename';
$events = 'blah';

$sql = "select * from '$prefix' where events = '$events'";
[/code]
?

post what you are trying to do
[/quote]


$prefix is the prefix of my table names and events is one of the tables so ie $prefix = phpfreaks_
so this would look like $sql='SELECT * FROM 'phpfreaks_events';

the problem isn't with the tables it's with extracting information that has "" in it.

this $data['name'] returns A-TRAK "SUNGLASSES IS A MUST TOUR"

but when I do this

$result=mysql_query("SELECT * FROM ".$prefix."events WHERE name='$name'") or die(query_error());
$row=mysql_fetch_array($result);

it just returns A-TRAK because of the "

hope I'm a bit more clear

[kenrbnsn]

When you store your data use the [a href=\"http://www.php.net/mysql_real_escape_string\" target=\"_blank\"]mysql_real_escape_string()[/a] to properly escape quotes and other characters that can affect the store operation. When this data is extracted, the escaped characters will not be escaped.

One more point, when you send data to the browser that includes quotes or double quotes, use the [a href=\"http://www.php.net/htmlentities\" target=\"_blank\"]htmlentities()[/a] function to convert these to encoded characters. If you had looked at your generated code, you would have seen that the double quote in your data ended the value in the form.

Ken

[digitalgod]

[!--quoteo(post=377045:date=May 25 2006, 11:15 AM:name=kenrbnsn)--][div class=\'quotetop\']QUOTE(kenrbnsn @ May 25 2006, 11:15 AM) [snapback]377045[/snapback][/div][div class=\'quotemain\'][!--quotec--]
When you store your data use the [a href=\"http://www.php.net/mysql_real_escape_string\" target=\"_blank\"]mysql_real_escape_string()[/a] to properly escape quotes and other characters that can affect the store operation. When this data is extracted, the escaped characters will not be escaped.

One more point, when you send data to the browser that includes quotes or double quotes, use the [a href=\"http://www.php.net/htmlentities\" target=\"_blank\"]htmlentities()[/a] function to convert these to encoded characters. If you had looked at your generated code, you would have seen that the double quote in your data ended the value in the form.

Ken
[/quote]

thanks Ken, I think it's going to work but can you help me use that in my code?

I tried doing this

[code]
$query = sprintf("SELECT * FROM ".$prefix."events WHERE name='$name'" , mysql_real_escape_string($name));
          //$result=mysql_query("SELECT * FROM ".$prefix."events WHERE name='$name'") or die(query_error());
          $row=mysql_fetch_array($query);
            if ($row['id'] != "") {
            $tmpl->add_template("editevent_form2");
            }
            else {
            $tmpl->add_template("username_no");
            }[/code]

but I get this

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

**edit**

oh I gotta use that only to store data right? or not.... really lost here lol

[eves]

[code]
$result=mysql_query($query);
[/code]

you missed that part

[digitalgod]

[!--quoteo(post=377178:date=May 25 2006, 08:37 PM:name=eves)--][div class=\'quotetop\']QUOTE(eves @ May 25 2006, 08:37 PM) [snapback]377178[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[code]
$result=mysql_query($query);
[/code]

you missed that part
[/quote]

still not working... any ideas on what I'm doing wrong

[code]$query = sprintf("SELECT * FROM ".$prefix."events WHERE name='$name'" , mysql_real_escape_string($name)) or die(query_error());
          $result= mysql_query($query);
          //$result=mysql_query("SELECT * FROM ".$prefix."events WHERE name='$name'") or die(query_error());
          $row=mysql_fetch_array($result);
            if ($row['id'] != "") {
            $tmpl->add_template("editevent_form2");
            }
            else {
            $tmpl->add_template("username_no");
            }[/code]

still only displays A-TRACK and not the rest with the double quotes

[kenrbnsn]

Try this:
[code]<?php
$query = "SELECT * FROM ".$prefix."events WHERE name='" . mysql_real_escape_string($name) . "'";
$result= mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());
$row=mysql_fetch_assoc($result);
if ($row['id'] != "")
   $tmpl->add_template("editevent_form2");
else
   $tmpl->add_template("username_no");
?>[/code]
You were using the sprintf() function incorrectly and you had the "or die" clause on the wrong line.

Ken

[digitalgod]

[!--quoteo(post=377459:date=May 26 2006, 08:48 PM:name=kenrbnsn)--][div class=\'quotetop\']QUOTE(kenrbnsn @ May 26 2006, 08:48 PM) [snapback]377459[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Try this:
[code]<?php
$query = "SELECT * FROM ".$prefix."events WHERE name='" . mysql_real_escape_string($name) . "'";
$result= mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());
$row=mysql_fetch_assoc($result);
if ($row['id'] != "")
   $tmpl->add_template("editevent_form2");
else
   $tmpl->add_template("username_no");
?>[/code]
You were using the sprintf() function incorrectly and you had the "or die" clause on the wrong line.

Ken
[/quote]


Hi Ken, thank you for your reply but I'm still getting an error :S.

here's the error I get :

Problem with the query: INSERT INTO mtlguest_events (club,name,date,time,performers,dj,promoter,music,cover,dress,notes,tickets,guestlist,flyer1,flyer2,flyer3,flyer4,type) VALUES('1234','A-TRAK \"SUNGLASS IS A MUST TOUR\"','06-04-06','','','','','','','','','yes','no','','','','','both')A-TRAK \\\"SUNGLASS IS A MUST TOUR\\\"'
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'A-TRAK \\\"SUNGLASS IS A MUST TOUR\\\"'' at line 1

and here's the code I have
[code]
$query = "INSERT INTO ".$prefix."events (club,name,date,time,performers,dj,promoter,music,cover,dress,notes,tickets,guestlist,flyer1,flyer2,flyer3,flyer4,type) VALUES('$club','$name','$date','$time','$performers','$djs','$promoter','$style','$price','$dress','$notes','$buy','$guest','$flyer1','$flyer2','$flyer3','$flyer4','$type')" . mysql_real_escape_string($name) . "'";
         $result= mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());[/code]

[kenrbnsn]

The values that are coming in from the form already have the quotes escaped, so you need to use stripslashes before you use mysql_real_escape_string. Since all strings coming in from a form should be sanitized before inserting them into the database, I would use a foreach statement and a switch statement to help build the mysql insert query:
[code]<?php
$qtmp = array();
foreach($_POST as $k => $v)
    switch($k) {
        case 'club':
        case 'name':
        case 'performers':
        case 'promoter':
        case 'dress':
        case 'notes':
        case 'flyer1':
        case 'flyer2':
        case 'flyer3':
        case 'flyer4':
        case 'type':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = $k . " = '" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'djs':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "dj = '" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'style':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "music = '" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'price':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "cover = '" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'buy':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "tickets = '" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'guest':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "guestlist = '" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'date':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "date = '" . date('Y-m-d',strtotime($v)) . "'";
            break;
        case 'time':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "time = '" . date('G:i:00',strtotime($v)) . "'";
            break;
     }
$query = "INSERT INTO ".$prefix."events set " . implode(', ',$qtmp);
$result = mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());
?>[/code]
Ken

[homchz]

How do you have your database table set up? I am using a text field and I can add quotes all I want. I have a feeling it is a mySQL issue not your PHP code.

[digitalgod]

I really appreciate your help Ken but it's still doing the same thing...... it's really getting frustrating.


admin.php
[code]
case "newevent";
      $process=$_POST['process'];
        if ($process == "yes") {
        $club=$_POST['club_name'];
        $name=$_POST['event_name'];
        $month=$_POST['month'];
        $day=$_POST['day'];
        $date=$month.'-'.$day.'-'.'06';
        $time=$_POST['time'];
        $performers=$_POST['performers'];
        $djs =$_POST['djs'];
        $promoter=$_POST['promoter'];
        $style=$_POST['style'];
        $price=$_POST['cover'];
        $dress=$_POST['dress'];
        $notes=$_POST['notes'];
        $buy=$_POST['buy'];
        $guest=$_POST['guestlist'];
        $flyer1=$_POST['flyer1'];
        $flyer2=$_POST['flyer2'];
        $flyer3=$_POST['flyer3'];
        $flyer4=$_POST['flyer4'];
        $type=$_POST['type'];
        $query = "SELECT * FROM ".$prefix."events WHERE name='" . mysql_real_escape_string($name) . "'";
         $result= mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());
         $row=mysql_fetch_assoc($result);
         if ($row['id'] != "") {
          $tmpl->add_template("event_no");
          }
          else {
            
          $qtmp = array();
foreach($_POST as $k => $v)
    switch($k) {
        case 'club_name':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "club ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'event_name':
            if (trim(stripslashes($v)) != '')
                $qtmp[] = "name ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
            break;
        case 'djs':
        case 'performers':
        case 'promoter':
        case 'dress':
        case 'notes':
        case 'buy':
        case 'guestlist':
        case 'flyer1':
        case 'flyer2':
        case 'flyer3':
        case 'flyer4':
        
     }
$query = "INSERT INTO ".$prefix."events set " . implode(', ',$qtmp);
$result = mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());

                    
          $tmpl->add_template("club_success");
          }
        }
        else {
        $tmpl->add_template("event_form");
        }
      break;
      case "editevent":
      $process=$_POST['process'];
        if ($process == "yes") {
        $process_b=$_POST['process_b'];
        $name=$_POST['ename'];
          if ($process_b == "yes") {
          $new_club=$_POST['club_name'];
          $new_name=$_POST['event_name'];
          $new_month=$_POST['month'];
          $new_day=$_POST['day'];
          $new_date=$month.'-'.$day.'-'.'06';
          $new_time=$_POST['time'];
          $new_performers=$_POST['performers'];
          $new_djs =$_POST['djs'];
          $new_promoter=$_POST['promoter'];
          $new_style=$_POST['style'];
          $new_price=$_POST['cover'];
          $new_dress=$_POST['dress'];
          $new_notes=$_POST['notes'];
          $new_buy=$_POST['buy'];
          $new_guest=$_POST['guestlist'];
          $new_flyer1=$_POST['flyer1'];
          $new_flyer2=$_POST['flyer2'];
          $new_flyer3=$_POST['flyer3'];
          $new_flyer4=$_POST['flyer4'];
          $new_type=$_POST['type'];
          if (strlen($new_name) < 4 ){
            $tmpl->add_template("edituser_tooshort");
            }
            else {
            mysql_query("UPDATE ".$prefix."events SET club='$new_club',name='$new_name',date='$new_date',time='$new_time',performers='$new_performers',dj='$new_djs',promoter='$new_promoter',music='$new_style',cover='$new_price',dress='$new_dress',notes='$new_notes',cover='$new_price',tickets='$new_buy',guestlist='$new_guest',flyer1='$new_flyer1',flyer2='$new_flyer2',flyer3='$new_flyer3',flyer4='$new_flyer4',type='$new_type' WHERE name='$name'") or die(query_error());
            $tmpl->add_template("editclub_success");
            }
          }
          else {
          $query = "SELECT * FROM ".$prefix."events WHERE name='" . mysql_real_escape_string($name) . "'";
          echo $query;
          $result= mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());
          $row=mysql_fetch_assoc($result);
          if ($row['id'] != "") {
                $tmpl->add_template("editevent_form2");
          }
          else
             $tmpl->add_template("username_no");
          }
        }
        else {
        $tmpl->add_template("editevent_form1");
        }      
      break;
[/code]

And this is the form to create an event (event_form.php)

[code]
<script type="text/javascript">
function writeMonthOptions()
{
  var theMonth;
  var monthCounter;
  var numMonth;
  var theDate = new Date();
  var month=new Array(12)
  month[0]="January"
  month[1]="February"
  month[2]="March"
  month[3]="April"
  month[4]="May"
  month[5]="June"
  month[6]="July"
  month[7]="August"
  month[8]="September"
  month[9]="October"
  month[10]="November"
  month[11]="December"  

  for (monthCounter = 0; monthCounter < 12; monthCounter++)
  {
    numMonth = monthCounter + 1;
    theMonth = month[monthCounter];
    if (numMonth < 10 )
    {
      numMonth = '0' + numMonth;
    }
    document.write('<OPTION value=' + numMonth + '>' + theMonth);
  }
}

function writeDay()
{
  var dayCounter;
  var numDay;
  //numDay = dayCounter;
  for (dayCounter = 1; dayCounter <= 31; dayCounter++)
  {
    if (dayCounter < 10)
    {
      numDay = '0' + dayCounter;
    }
    else
    {  
      numDay = dayCounter;
    }
    document.write('<OPTION value=' + numDay + '>' + dayCounter);
  }
}
</script>

<div id="article">
<form action="admin.php?a=newevent" method="post" enctype="multipart/form-data" name="form" id="form">
<input type="hidden" name="process" value="yes" />
    <table width="603" border="0">
      <tr>
        <td colspan="2">&nbsp;</td>
      </tr>
      <tr>
        <td width="151">Club Name:</td>
        <td width="442"><input name="club_name" type="text" id="club_name"/></td>
      </tr>
      <tr>
        <td>Event Name:</td>
        <td><input name="event_name" type="text" id="event_name"></td>
      </tr>
      <tr>
        <td>Date:</td>
        <td><select name="month" id="month">
            <option>Month</option>
            <script type="text/javascript">writeMonthOptions();</script>
          </select> <select name="day" id="day">
            <option>Day</option>
            <script type="text/javascript">writeDay();</script>
          </select></td>
      </tr>
      <tr>
        <td>Time :</td>
        <td><input name="time" type="text" id="time"></td>
      </tr>
      <tr>
        <td>Performers :</td>
        <td><input name="performers" type="text" id="performers"></td>
      </tr>
      <tr>
        <td>Dj:</td>
        <td><select name="dj" id="dj">
            <option>Choose one</option>
          </select></td>
      </tr>
      <tr>
        <td>Promoter: </td>
        <td><input name="promoter" type="text" id="promoter" /></td>
      </tr>
      <tr>
        <td>Music style:</td>
        <td><input name="style" type="text" id="style" /></td>
      </tr>
      <tr>
        <td>Cover price: </td>
        <td> <input name="cover" type="text" id="cover"> </td>
      </tr>
      <tr>
        <td>Dress Code:</td>
        <td><input name="dress" type="text" id="dress" /></td>
      </tr>
      <tr>
        <td>Notes:</td>
        <td><textarea name="notes" id="notes"></textarea></td>
      </tr>
      <tr>
        <td>Buy Tickets:</td>
        <td><select name="buy" id="buy">
            <option>Choose one</option>
            <option value="yes">Yes</option>
            <option value="no">No</option>
          </select></td>
      </tr>
      <tr>
        <td>Guestlist:</td>
        <td><select name="guestlist" id="guestlist">
            <option>Choose one</option>
            <option value="yes">Yes</option>
            <option value="no">No</option>
          </select></td>
      </tr>
      <tr>
        <td>Flyers:</td>
        <td><table width="100%" border="0">
            <tr>
              <td><input type="file" name="file"></td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td><input type="file" name="file2"></td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td><input type="file" name="file3"></td>
              <td>&nbsp;</td>
            </tr>
            <tr>
              <td><input type="file" name="file4"></td>
              <td>&nbsp;</td>
            </tr>
          </table></td>
      </tr>
      <tr>
        <td>Type:</td>
        <td><select name="type" id="type">
            <option>Choose one</option>
            <option value="hip hop">Hip Hop</option>
            <option value="house">House</option>
            <option value="both">Both</option>
          </select></td>
      </tr>
      <tr>
        <td colspan="2">&nbsp;</td>
      </tr>
    </table>
<input type="submit" value="Create"/>
<label>
<input type="reset" name="Reset" value="Reset" />
</label>
</form>
</div>
[/code]

and this is the form to select what event to edit (editevent_form1.php)

[code]
<div id="article">
<form action="admin.php?a=editevent" method="post">
<input type="hidden" name="process" value="yes" />
<table border="0">
<tr>
        <td>Event:</td>
        <td><select name="ename" id="ename">
            <option>Choose one</option>
            <?php
$sql='SELECT * FROM '.$prefix.'events';
$req=mysql_query($sql) or die(query_error());
while($data = mysql_fetch_assoc($req)) {
    echo '<option value="'.$data['name'].'">'.$data['name'].'</option>';
}
?>
          </select></td>
</tr>
</table>
<br />
<input type="submit" value="Edit Event" />
</form>
</div>
[/code]

[digitalgod]

hey Ken,

I tried everything in my form htmlentities which displayed A-TRAK \\\"SUNGLASSES ARE A MUST TOUR\\\" so it said it doesn't exist in my database

also tried addslashes but only gave me \\\\\\

I tried switching ' with " in my form but gave me the same result as htmlentities....

why is it so complicated to make this thing work :(

[homchz]

Can you see how it is being put into the database, before you extract it?

I still think it is a db issue and not PHP I do not have to add any special funtions to pull quotes or quoted phrases out of my db tables.

[digitalgod]

[!--quoteo(post=378156:date=May 29 2006, 02:29 PM:name=homchz)--][div class=\'quotetop\']QUOTE(homchz @ May 29 2006, 02:29 PM) [snapback]378156[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Can you see how it is being put into the database, before you extract it?

I still think it is a db issue and not PHP I do not have to add any special funtions to pull quotes or quoted phrases out of my db tables.
[/quote]


sure

[code]
$qtmp = array();
            foreach($_POST as $k => $v)
                switch($k) {
                    case 'club_name':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "club ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'event_name':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "name ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'dj':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "dj ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'performers':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "performers ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'promoter':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "promoter ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'dress':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "dress ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'notes':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "notes ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'buy':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "buy ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;
                    case 'guestlist':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "guestlist ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;  
                    case 'file1':
                        if (trim(stripslashes($v)) != '')
                            $qtmp[] = "flyer1 ='" . mysql_real_escape_string(trim(stripslashes($v))) . "'";
                    break;      
     }
     $query = "INSERT INTO ".$prefix."events set " . implode(', ',$qtmp);
     $result = mysql_query($query) or die("Problem with the query: $query<br>" . mysql_error());[/code]

even tried doing

case 'event_name':
if (trim(stripslashes($v)) != '')
$qtmp[] = "name ='" . $v . "'";

but still got the same thing

[homchz]

I meant have you seen that what you are putting in the table is what is you put in the form.

[digitalgod]

[!--quoteo(post=378167:date=May 29 2006, 03:04 PM:name=homchz)--][div class=\'quotetop\']QUOTE(homchz @ May 29 2006, 03:04 PM) [snapback]378167[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I meant have you seen that what you are putting in the table is what is you put in the form.
[/quote]


well if I look at my table this is exacty what I see in the "name" field

A-TRAK "SUNGLASSES ARE A MUST TOUR"

[digitalgod]

alright finally fixed it!! was as series of addslashes, stripslashes and htmlentities here and there...