SQL problem - What am I missing?

kru · March 12, 2013

I installed the SQL Interface V1.00 Add-On (http://addons.oscommerce.com/info/2342/v,22) but I can't get SQL statements to run correctly.

For example:

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

yields a result of:

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

However, I can run the exact same statement (copied and pasted) in mysqladmin (through the OPS panel of my hosting company) and get one order_id containing the string.

Why can I run it through mysqladmin and not through the entry box created by the Add-On?

Bob Terveuren · March 12, 2013

Hi

'\'%12345%\'' - looks like it's escaping the ' character ??

kru · March 12, 2013

Hi

'\'%12345%\'' - looks like it's escaping the ' character ??

OK but what do you put in front of it (or around it) to prevent that. I'm assuming there's another way it needs to be written but I guess my point is, I don't know what that is. I wrote the SQL statement like I normally do when I run it in mysqladmin because it ran/runs fine there. So what's different and what do I need to correct to allow it to run in the Add-On?

Thanks.

kru · March 12, 2013

I'm assuming this has something to do with running SQL via a php script versus running it in mysqladmin. I've been searching the internet for hours and can't seem to make sense of this. With my luck, it'll end up being something easy and I'll feel like an idiot...

kru · March 13, 2013

Anyone?

Jack_mcs · March 13, 2013

I'm not familiar with that addon but it needs to use the tep_db_input function when adding to the database and, I'm assuming, it isn't. Compare the code that makes that call to one of the others in the shop to see how that function is used.

kru · March 13, 2013

It's not adding to the database. Merely selecting records. But it does appear there is a problem with the program. When I change the SQL query to get rid of the LIKE clause and select the comments from a particular orders_id, it runs but it doesn't display the actual comments on the screen. It just says "Your query was successful! Rows Affected: 5."

If you could make some recommendations on how to modify this program, it would be great. It sounds like exactly what I need to search the comments field from the Admin page. We would like to start storing some information there but can only do so if we can do a quick and easy search to find records with particular strings in that field. Or, if you have an idea of another program that would accomplish this, even better.

Here's the code from the 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">

<head>

</head>

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

</table></td>

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

<tr>

<tr>

</tr>

<tr>

<td class="main" colspan="2"><?php echo '<b>' . $text_heading . ':</b>'; ?></td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

</table></td>

</tr>

</table></td>

</tr>

</table></td>

</tr>

</table>

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

<br>

</body>

</html>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Jack_mcs · March 14, 2013

Or reading from - same thing in this case. I haven't tried it but try changing

sqlquery($HTTP_POST_VARS['query_entry']);

to

$sql = tep_db_prepare_input($HTTP_POST_VARS['query_entry']);
sqlquery(tep_db_input($sql);

kru · March 14, 2013

Or reading from - same thing in this case. I haven't tried it but try changing
sqlquery($HTTP_POST_VARS['query_entry']);
to
$sql = tep_db_prepare_input($HTTP_POST_VARS['query_entry']);
sqlquery(tep_db_input($sql);

Tried it. Got this:

Parse error: syntax error, unexpected ';' in /blahblahblah/yadayada/admin/sql_interface.php on line 24

kru · March 14, 2013

Added the missing paren. Page loaded this time but the error after I submit it is the same as before:

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 '\'%1Z4F5F250352214642%\'' at line 1

While executing:

SELECT orders_id FROM `orders_status_history` WHERE `comments` LIKE \'%1Z4F5F250352214642%\'

This is after pasting the following code into the text box and submitting it (hitting SEND button):

SELECT orders_id FROM `orders_status_history` WHERE `comments` LIKE '%1Z4F5F250352214642%'

What is the following code from lines 9-13 doing? Does it need re-worked? The error message is being generated there.

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

kru · March 14, 2013

Aside from the issue of how to format the text string for the LIKE statement, it is also a problem that it doesn't write the results of a successful query to the screen. A query like:

SELECT * FROM `orders_status_history` WHERE `orders_id` = 31166

generates a success message and indicates there are 5 records affected but it doesn't show me the results on the screen.

This Add-On is not described very well. Maybe he never intended it to return results. Maybe he was just using it for updates. It may not be what we're looking for but surely there's something out there that will suit our needs. I was hoping for a report but this Add-On had me thinking we could submit an SQL query from the Admin panel instead.

Specifically, we are trying to search the comments field in the Orders table. Ideally it would be a report with a text field where we enter a value, click a button to run it and it searches all records for a match, returning them to the screen. There are several 'canned' reports in osCommerce (Products Viewed, etc) but they run automatically when you click on the link. None allow an input value for a search. If I could find a report that allowed an input value, I could possibly modify it for our purposes. But I've searched several times with no luck.

Anyone have any ideas or want to generate a report that would do something like this? I'm sure others would find it useful to search order comments too...

Thanks.

Bob Terveuren · March 16, 2013

Hi

There's nothing in the code to return the actual data found -$query_result is always going to be either an error or the number of rows found

MrPhil · March 16, 2013

If you get a report of something like

...comments LIKE \'%12345%\'

that usually means that at least twice your string has been "prepped" for the database, something like \\'%12345%\\'. You want the actual string in the end to be ...comments LIKE '%12345%', but it's got some \ unnecessarily added to it. Usually this is caused by a programmer who isn't properly keeping track of whether they've already prepped the string for database use, or maybe "do it one [more] time just to be sure", or even not realizing when they do and don't need to "prep".

It might also be something to do with PHP "magic quotes" settings on your server, that it's automatically adding escapes \ to the string. You might read up on that, especially as the behavior has changed with recent PHP 5.x releases. Hopefully this will give you a few more places to look...

Chris H · March 18, 2013

This tester

$result = mysql_query('SELECT countries_iso_code_3 FROM countries WHERE countries_name LIKE "Austr%"');

is working all right for me, in that it is returning

AUS

AUT

so it seems to be helpful to put the comparator string inside double-quotes.

MrPhil · March 18, 2013

Either

'SELECT countries_iso_code_3 FROM countries WHERE countries_name LIKE "Austr%"'

or

"SELECT countries_iso_code_3 FROM countries WHERE countries_name LIKE 'Austr%'"

would work (I believe that the second version, using ' within the SQL query, is somewhat preferred).

Note that this is a direct call to mysql_query(). In the OP's case, they appear to be entering a query string which is being processed in some way (adding \ to ') before being passed to mysql_query(), and that's a problem. I was unable to find where this was happening with a quick look at the code given.

SQL problem - What am I missing?

Recommended Posts

kru

Bob Terveuren

kru

kru

kru

Jack_mcs

kru

Jack_mcs

kru

kru

kru

Bob Terveuren

MrPhil

Chris H

MrPhil

Archived

Browse

Activity