Jump to content

How to create a simple UI for a simple SQL query

noesac

By noesac
in Core developers

 Share

Recommended Posts

noesac Newbie

noesac

Posted
Posted

I have a few basic SQL queries that I run on a regular basis.

I would like to build my own page for this. Basically i'm after just a very simple page that runs the query, and returns the results in a grid. Any tips on how to do this? Are there any examples in the current Prestashop that I could use as a guide?


I think it would be quite simple:









Anyone?

Link to comment
Share on other sites
MrBaseball34 Newbie

MrBaseball34

Posted
Posted

Here's a simple one I whipped up in about 10 minutes.
No error checking so use at your own risk...


<html>
<head>
  <title>SQL Runner</title>
</head>
<body>
<?php 
  $sql = '';
  if(isset($_POST['sql'])) {
    $sql = $_POST['sql'];
  }

  // You can fill in your values here
  $dbname = '';
  $dbuser = '';
  $dbpwd = '';

  // if you fill in the values, you can remove these lines
  if(isset($_POST['dbname'])) {
    $dbname = $_POST['dbname'];
  }
  if(isset($_POST['dbuser'])) {
    $dbuser = $_POST['dbuser'];
  }
  if(isset($_POST['dbpwd'])) {
    $dbpwd = $_POST['dbpwd'];
  }
?>

<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post" name="self" id="self">
  Database name: <input type="text" name="dbname" size="30" value="<?php echo $dbname ?>"><br>
  DB User: <input type="text" name="dbuser" size="20" value="<?php echo $dbuser ?>"><br>
  DB Password: <input type="text" name="dbpwd"  size="20" value="<?php echo $dbpwd ?>"><br>
  SQL:

 <textarea  name="sql" cols="100" rows="15"><?php echo $sql ?></textarea><br>
  <input type="submit" name="submit" value="Go">
</form>
<?php
  $dbhost = 'localhost';

  if(isset($_POST['submit'])) { 
    if(strlen($sql) > 0) {
      if (!mysql_connect($dbhost, $dbuser, $dbpwd)) {
        die("Can't connect to database");   
      }
      if (!mysql_select_db($dbname)) {
        die("Can't select database");   
      }
      $result = mysql_query($sql);
      if (!$result) {
        die("Query to show fields from table failed");
      }        
      $fields_num = mysql_num_fields($result);

      echo "
{$field->name}
$cell

Link to comment
Share on other sites
  • 2 weeks later...
MrBaseball34 Newbie

MrBaseball34

Posted
Posted

without creating a full-blown SQL parser, no, but you can give this a try
Make this change:


if (!mysql_select_db($dbname)) {
 die("Can't select database");   
}
if(strpos(strtolower($sql), 'insert') > 0 || strpos(strtolower($sql), 'delete) > 0) {
 die('Inserts or deletes not allowed');
} else {
 $result = mysql_query($sql);
}
if (!$result) {
 die("Query to show fields from table failed");
}        
$fields_num = mysql_num_fields($result



or better, yet, only allow select like this:


if (!mysql_select_db($dbname)) {
 die("Can't select database");   
}
if(strpos(strtolower($sql), 'select') === 0) {
 die('only select queries are allowed');
} else {
 $result = mysql_query($sql);
}
if (!$result) {
 die("Query to show fields from table failed");
}        
$fields_num = mysql_num_fields($result

Link to comment
Share on other sites
noesac Newbie

noesac

Posted
  • Author
Posted

Thanks, so just to make sure I have this right, I would replace: 


if (!mysql_select_db($dbname)) {
       die("Can't select database");   
     }
     $result = mysql_query($sql);
     if (!$result) {
       die("Query to show fields from table failed");
     }        
     $fields_num = mysql_num_fields($result);




With: 


if (!mysql_select_db($dbname)) {
 die("Can't select database");   
}
if(strpos(strtolower($sql), 'select') === 0) {
 die('only select queries are allowed');
} else {
 $result = mysql_query($sql);
}
if (!$result) {
 die("Query to show fields from table failed");
}        
$fields_num = mysql_num_fields($result);




Is this right?

Link to comment
Share on other sites
noesac Newbie

noesac

Posted
  • Author
Posted

I just tested it and I don't think it works, I tried to run the following Select statement and got an error: 

select DISTINCT ps_product.id_product, ps_product_lang.name, ps_product_lang.description, ps_product.price, ps_product.quantity, ps_product.reference, ps_product.active, ps_product.id_category_default 

FROM ps_product, ps_product_lang, ps_category_product, ps_category_lang

WHERE ps_product.id_product=ps_product_lang.id_product AND ps_product.id_product=ps_category_product.id_product AND ps_category_product.id_category=ps_category_lang.id_category



"only select queries are allowed"

Link to comment
Share on other sites
MrBaseball34 Newbie

MrBaseball34

Posted
Posted

Worked just fine for me in my test. Do you not know PHP?

Make this change:

if($result) {
 while($row = mysql_fetch_row($result))
 {
     echo "";

     // $row is array... foreach( .. ) puts every element
     // of $row to $cell variable
     foreach($row as $cell)
         echo "$cell";

     echo "\n";
 }
 mysql_free_result($result);
} else {
 die('no results or problem with results');
}

Link to comment
Share on other sites
noesac Newbie

noesac

Posted
  • Author
Posted

I think I found a bug, but I think I also found the solution, can you please double check this to make sure I'm not going to cause problems with my DB.

Basically this worked perfectly on my test server, but when I tried it on my live server (a physically separate server), it kept adding a backslash to all my quotes!!

e.g. ps_customer.name = 'bob'

would become:

ps_customer.name = /'bob/'

This is the problem:

http://www.securityfocus.com/archive/1/511061

This is the solution:

http://www.bigresource.com/Tracker/Track-php-U5d0eLKy/

So I added this line under the first IF statement: 


$sql = stripslashes($_POST['sql']);



Does my fix look correct?

I also added this OR statement (sorry about all the questions...I'm not a very good coder and I always want to triple check before making any risky changes!):

From: 

      if(strpos(strtolower($sql), 'select')===0)  {




To: 

      if((strpos(strtolower($sql), 'select')===0) || (strpos(strtolower($sql), 'select distinct')===0)) {

Link to comment
Share on other sites
  • 5 years later...
Guest locen

Guest locen

Posted
Posted (edited)

Hi, I made this query:

 SELECT o.reference,  pl.name as product_name, pc.name AS city, od.product_quantity, cs.firstname, cs.lastname, DATE(o.date_add) AS data_acq,

ROUND (od.total_price_tax_incl, 3) AS price_with_tax,  cu.name AS curr, o.total_discounts_tax_excl, pt.rate AS tax_value_percent, od.product_reference

FROM ps_product p
LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_order_detail od ON p.id_product = od.product_id
LEFT JOIN ps_orders o ON o.id_order = od.id_order
LEFT JOIN ps_currency cu ON cu.id_currency = o.id_currency
JOIN ps_order_detail_tax dt ON od.id_order_detail = dt.id_order_detail
JOIN ps_tax pt ON pt.id_tax = dt.id_tax
JOIN ps_customer cs on o.id_customer=cs.id_customer
JOIN ps_address ad on o.id_address_delivery=ad.id_address
JOIN ps_country_lang pc on ad.id_country=pc.id_country
 JOIN ps_country_lang ps on pl.id_lang=pc.id_lang
WHERE  o.current_state = 2 OR o.current_state =  3 OR o.current_state = 4 OR o.current_state = 5 OR o.current_state = 9
OR o.current_state = 12 OR o.current_state = 13 OR o.current_state = 16 
 
 
GROUP BY od.id_order_detail
ORDER BY o.date_add;
 
 
But I have problem with prices. when I export file i have price number with a lot of decimals.
can someone help me?
Edited by locen (see edit history)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...