0

I have a PHP script installed on the admin side of our website that is supposed to allow us to run SQL queries. However, I'm not getting the same results as I am when running it through mysqladmin on our host company's OPS page.

When I submit:

SELECT orders_id FROM `orders_status_history` WHERE `comments` LIKE '%12345%'

I get one result (my test record) in mysqladmin. So it's successful.

However, when I submit it through the PHP program, I get:

MySQL error 1064: 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 '\'%12345%\'' at line 1

While executing:

SELECT orders_id FROM `orders_status_history` WHERE `comments` LIKE \'%12345%\'

I'm assuming the syntax is different when submitting through PHP but I can't, for the life of me, figure out what it's supposed to be. I've tried prefacing the single quotes with a slash. I've tried double quotes. I've spent a couple hours surfing the web. I've tried so many things I can't even keep them straight anymore. I'm assuming it is something simple. Can anyone point me in the right direction?

Here is the code from the php program. The user pastes the SQL query in a text area and hits a SEND button. Again, the exact same query works in mysqladmin but not when using this PHP program:

<?php
/*
  $Id: sql_interface.php,v 1.00 2004/08/13 00:28:44 draven Exp $
*/

  require('includes/application_top.php');
  $text_heading = INITIAL_TITLE;

    function sqlquery($query) {
    $result = mysql_query($query);
    global $query_result;
      if (mysql_errno()) {
        $query_result = "MySQL error ".mysql_errno().": ".mysql_error()."\n\nWhile executing:\n\n$query\n------------------------------------------------------------------------------------------\n\n";
      } else {
        $query_result = "Your query was successful!\nRows Affected: " . mysql_affected_rows();
      }
    return $result;
  }

  $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : '');

  if (isset($HTTP_POST_VARS['action']) && ($HTTP_POST_VARS['action'] == 'process')) {
    sqlquery($HTTP_POST_VARS['query_entry']);
    $text_heading = POST_QUERY_TITLE;
    $tryagain = TRY_AGAIN_TEXT;
  }
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo HEADING_TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
    <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
    </table></td>
<!-- body_text //-->
    <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"><?php echo tep_draw_form('sql_interface', 'sql_interface.php', 'post') . tep_draw_hidden_field('action', 'process'); ?>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td class="pageHeading" colspan="3"><?php echo HEADING_TITLE; ?></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
                  <tr>
                    <td class="main" colspan="3"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
                  </tr>
                  <tr>
                    <td class="main" colspan="2"><?php echo '<b>' . $text_heading . ':</b>'; ?></td>
                    <td class="main" align="right" colspan="1"><i><?php echo $tryagain; ?></i></td>
                  </tr>
                  <tr>
                    <td class="main" colspan="3"><?php echo tep_draw_textarea_field('query_entry', '', 137, 30, $query_result, '', false); ?></td>
                  </tr>
                  <tr>
                    <td class="main" colspan="3"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>
                  </tr>
              <tr>
                <td width="10"><?php echo tep_draw_separator('pixel_trans.gif', '10', '1'); ?></td>
                <td colspan="2"align="right"><?php echo tep_image_submit('button_send.gif', IMAGE_BUTTON_EXECUTE_SQL) . tep_draw_separator('pixel_trans.gif', '10', '1'); ?></form></td>
              </tr>
              <tr>
                <td class="smallText" colspan="3">&nbsp;</td>
              </tr>
            </table></td>
          </tr>
        </table></td>
      </tr>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
Chris Laplante
  • 29,338
  • 17
  • 103
  • 134

2 Answers2

2

I'm not sure if you're supposed to be escaping your single quotes on that particular query, as I'm not sure what the exact php code you're using is.

$query = "SELECT orders_id FROM orders_status_history WHERE comments LIKE '%12345%'";

That will work just fine. You would only need to escape them if you were doing:

$query = 'SELECT orders_id FROM orders_status_history WHERE comments like \'%12345%\'';

If this doesn't solve your issue, please post the relevant php code.

Edit: Try using stripslashes() on your query, and take a look at mysql_real_escape_string() leaving slashes in MySQL

Community
  • 1
  • 1
Sturm
  • 4,105
  • 3
  • 24
  • 39
  • I didn't escape anything. I assume mysql is attempting to escape it. My query is the first one that appears in my question. The second one is what is spit out with the error message. – Kevin Kevin Kevin Mar 13 '13 at 00:14
  • I tried the suggested code from Highway of Life. The results were uglier, if you ask me: MySQL error 1064: 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 '$query = \"SELECT orders_id FROM orders_status_history WHERE comments LIKE \'%12' at line 1 While executing: $query = \"SELECT orders_id FROM orders_status_history WHERE comments LIKE \'%12345%\'\"; – Kevin Kevin Kevin Mar 13 '13 at 00:15
  • Please do not vote for this answer, as it does not work. I copied and pasted his exact query (even though I had already tried exactly what was suggested before) and it fails at the very beginning of the statement. My original query manages to make it to the LIKE statement. I have tried changing the text string after the LIKE statement. I have tried it with and without an apostrophe, with quotes, with a slash, with a slash and an apostrophe, and with a slash and a quote. Please help. Thanks. – Kevin Kevin Kevin Mar 13 '13 at 14:16
0

In case the above answer doesn't work for anyone, check that all the default values match the data type and that all values are set to database type. One of mine wasn't set to InnoDB so it didn't work, but it did not return any error!

Patrick Geyer
  • 1,515
  • 13
  • 30