Problem with PDO and variables,

PHP
1

I am new to PDO and am having a few issues, I have googled and read a lot but don’t seem to be able to see the forest for the trees. My script builds the SQL query as expected, However it doesn’t work once I try and add it to the PDO Query.

 $s1 = $_REQUEST["search1"];
 $s2 = (explode(" ",$s1));

 foreach ($s2 as $s2b => $s2a)
 {
 	If ($s2b < '1') {
  		$s2c = "'SELECT * FROM st WHERE stext LIKE \'%".$s2a."%\' ";
 	}

 	If ($s2b > '0') {
  		$s2c = $s2c."AND stext LIKE \'%".$s2a."%\ ";
 	}	
 }
 	$s2c = $s2c." ORDER by st_price ASC ";
 	$s2ca = $s2c." LIMIT 0,2'";
    $s2cb = $s2c." LIMIT 2,2'";
    $s2cc = $s2c." LIMIT 4,2'";
    $s2cd = $s2c." LIMIT 6,2'";
    
   // on echo prints SQL queries (variables) as I expect them
   
   // pdo connect is in constants.php
 include ("navigation/constants.php"); 


 // If I add query manually it works and prints info as required
 		
 		$sqls= $pdo->query('SELECT * FROM st WHERE stext LIKE \'%web%\' AND stext LIKE \'%site%\' ORDER by st_price ASC LIMIT 2,2');
 		echo $sqls."<br />";
 	    while ($rows = $sqls->fetch()) 
 	   {
 	       echo "<div id=\"block1\"><h2>{$rows['stame']}</h2></div>";
 	    }  

 // When I try and use the variable it fails
 			 	$sqls= $pdo->query($s2cb);
 			 	echo $sqls;
 	    while ($rows = $sqls->fetch()) 
 	    {
 	       echo "<div id=\"block2\"><h2>{$rows['stame']}</h2></div>";
 	    }
2

Off Topic:

Welcome to the forums, @photography.

When you post code on the forums, you need to format it so it will display correctly. (I’ve edited your post above for you.)

You can highlight your code, then use the </> button in the editor window, or you can place three backticks ``` (top left key on US/UK keyboards) on a line above your code, and three on a line below your code. I find this approach easier, but unfortunately some European and other keyboards don’t have that character.

3

Thank you squire

1 Like
4

Enable PDO’s error reporting, so it tells you what’s wrong:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
5

PHP Fatal error: Call to a member function fetch() on a non-object

6

yea, false is not a PDOStatement object …

and you need to set the error reporting before you do the query.

7 
[17-Apr-2018 13:36:21 Africa/Johannesburg] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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 ''SELECT * FROM stock WHERE stock_text LIKE \'%web%\' AND stock_text LIKE \'%site' at line 1' in /home/pretoriacomputer/public_html/search.php:54
Stack trace:
#0 /home/pretoriacomputer/public_html/search.php(54): PDO->query(''SELECT * FROM ...')
#1 {main}
  thrown in /home/pretoriacomputer/public_html/search.php on line 54
8

Do you notice something off before (that’s where the error usually happens) SELECT?

9

I have tried escaping the ’ and even removing them before and after the statement. No difference, what am I missing? I bet it is something insignificant and I am going to feel like a total moron

10

if it’s not at the beginning, try looking at the end of the cited SQL.

11

'SELECT * FROM stock WHERE stock_text LIKE '%web%' AND stock_text LIKE '%site%\ ORDER by stock_price ASC LIMIT 0,2'

With escaped ’

The sql statement works as expected in phpmyadmin when I remove the .

12

Then you use a different query. just have a look at the code highlighting:

LIKE \'%site%\ ORDER
13

problem solved. escaping the ’ in the query build broke it

14

Now you can address you gaping SQL injection vulnerability.

2 Likes
15

User permissions set to SELECT only on database, Is that not enough?

16

That prevents editing data, but it doesn’t prevent fetching data that shouldn’t be fetched (e.g. passwords, data from other customers)

17

This particular database will allow them to get a list of my blogs and stock, there is no user data in the database at this stage but I am reading about SQL injection, I chose PDO because the info I got was that it prevented SQL injection. Is it no better than Mysqli in that respect then?

18

Both PDO and myqsli provide you with the means (namely prepared statements) to reliably prevent SQL injection (unlike the old mysql functions). However, that still requires you to correctly apply it. Neither PDO nor mysqli magically do that on their own.

2 Likes
19

Am I allowed to use bad words here? This means more learning and my 53 year old brain is easily confused.

20

If you want to write secure applications, then you have to learn constantly, as hackers continuously invent new ways to hack your app (for example MD5, 30 years ago cracking an MD5 hashed password took too long to be worth it, now it’s done in milliseconds).