MySQL Select Statement - Help Needed with Syntax

  • anyusernamewilldo
  • Novice
  • Novice
  • anyusernamewilldo
  • Posts: 22

Post 3+ Months Ago

Hi guys, I was wondering if someone could point me in the right direction...

In my select statement I am trying to acheive a variable as the value of my WHERE clause...

eg:
Code: [ Select ]
WHERE SomeField='".$EnteredValue."'


eg:
Code: [ Select ]
$query = "SELECT * FROM `SomeTable`
WHERE SomeField='".$EnteredValue."' ORDER by ID DESC";
  1. $query = "SELECT * FROM `SomeTable`
  2. WHERE SomeField='".$EnteredValue."' ORDER by ID DESC";


However it's not getting the value of $EnteredValue. Instead it just reads it as if it were static text... for example if i replace $EnteredValue with some bog standard text it works fine. So it's not seeing my $EnteredValue as a variable...

Please someone steer me in the right direction...
Thanks in advance.

Jay
  • anyusernamewilldo
  • Novice
  • Novice
  • anyusernamewilldo
  • Posts: 22

Post 3+ Months Ago

Ah, Nevermind folks... Solved it.

Code: [ Select ]
"SELECT * FROM `SomeTable` WHERE SomeField='$EnteredValue' ORDER by ID DESC"



So this works:
Code: [ Select ]
WHERE SomeField='$EnteredValue'


And this doesn't:
Code: [ Select ]
WHERE SomeField='".$EnteredValue."'
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

I'm over here scratching my head as to why it works now. The change you've made here shouldn't actually make a difference.
  • anyusernamewilldo
  • Novice
  • Novice
  • anyusernamewilldo
  • Posts: 22

Post 3+ Months Ago

Your Right...

After some further testing both methods work... I think the problem was the $EnteredValue wasn't being passed / received from the previous page properly.

Still not bad - ended up with 2 working solutions :)
cheers for taking a look joe


Jay
Moderator Remark: clipped Ozzu-specific section to create new thread with
  • onlyican.com
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1589
  • Loc: Hants, UK

Post 3+ Months Ago

Reasons why you should turn error Reporting onto strict (E_ALL) and always escape out of a string to display a variable.

Note: If your variable was undefined (not parsed) then it would be NULL
The error where the variable name shows normally occurs when
A) The variable is not escape out of the string
B) A Space occurred between the $ sign and the start of your variable
  • sandy_j_sam
  • Newbie
  • Newbie
  • sandy_j_sam
  • Posts: 9

Post 3+ Months Ago

Hi, you just replace that code with that script


<?php
$EnteredValue ="Jai Sai Ram";
$conn = mysql_connect('localhost','root','');
$db = mysql_select_db('db_name',$conn);
$sql = mysql_qyery( "SELECT * FROM Sometime WHERE SomeField = $EnteredValue ORDER by ID DESC ",$conn);
?>
According to me that help for you.
  • onlyican.com
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1589
  • Loc: Hants, UK

Post 3+ Months Ago

hi Sandy

1) it is bad practice to define your connection each time you run a query
What if you change Something like the Username / Password

2) It is better practice to define your SQL query as a variable, this allows you to echo out your query and see any errors, such as noticing your variable is not being parsed

3) If you are adding a variable to your query, you need to wrap it in single quotes to tell SQL it is a string
MY Method, which is a "preferred" method

Global Constants File, which contains the following

PHP Code: [ Select ]
define('DB_HOST', 'localhost');
define('DB_USER', 'database_user');
define('DB_PASS', 'database_pass');
define('DB_NAME', 'database_name');
 
  1. define('DB_HOST', 'localhost');
  2. define('DB_USER', 'database_user');
  3. define('DB_PASS', 'database_pass');
  4. define('DB_NAME', 'database_name');
  5.  

Prime example where I recently used this, I updated my website in a development area, I didnt want changes on the development affecting the live site, so I copied the Database, added 'v2' to the end and changed the DB_Name in the above file

Secondly, I have a SQL Class, which handles all my queries

so I simply call like such
PHP Code: [ Select ]
//Protect against SQL Injection, we check the string, mainly for user entered data
$strMyVar = $objDBClass->purifyString($strMyVar);
$strSql = "SELECT * FROM tblName WHERE fldName = '".$strMyVar."' ORDER BY ID DESC"
//Parse the query to my class
$objDBClass->setSql($strSql);
//Run Query
if($objDBClass->setData()){
$arrResults = $objDBClass->getData();
}else{ //An error has occured
//I want to debug, this will be removed before it goes live, I actually have a function for this bit
echo 'SQL was '.$strSql.'<br />';
echo $objDBClass->getErrorMessage().'<br />'; //Only gets the standard PHP MySQL Error
 
}
 
  1. //Protect against SQL Injection, we check the string, mainly for user entered data
  2. $strMyVar = $objDBClass->purifyString($strMyVar);
  3. $strSql = "SELECT * FROM tblName WHERE fldName = '".$strMyVar."' ORDER BY ID DESC"
  4. //Parse the query to my class
  5. $objDBClass->setSql($strSql);
  6. //Run Query
  7. if($objDBClass->setData()){
  8. $arrResults = $objDBClass->getData();
  9. }else{ //An error has occured
  10. //I want to debug, this will be removed before it goes live, I actually have a function for this bit
  11. echo 'SQL was '.$strSql.'<br />';
  12. echo $objDBClass->getErrorMessage().'<br />'; //Only gets the standard PHP MySQL Error
  13.  
  14. }
  15.  


As you can see above, its clean, the connection and everything is in 1 class, and I echo out the query for debugging if I have to
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

I'm not a big fan of using constants for passwords anymore. I like to use a stdClass or an array to hold the database details in a config file, then include() that config file right before the database connection is made, and unset() the stdClass holding the database details immediately after.

I just don't like the idea of having the details lingering around any longer than they have to be.
  • onlyican.com
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1589
  • Loc: Hants, UK

Post 3+ Months Ago

That is a fair point Joebert, but people should not be able to access Constant Data any easier than they can an array from a config.

I have known poeple to be soo paranoid, they took the extreme of encrypting there password in a constant and decrypted the password when using in the database.

This is not needed.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

It's all moot if someone learns the name of the constant and finds a lapse in security somewhere they can print that constant.

If I load it, use it, delete it, then the chance of my application exposing it, is zero percent. The only way it could be exposed, is via lax filesystem permissions, or a vulnerability in the MySQL library or the PHP implementation of it, which would exist no matter how I write my application. :)

How can I unset() a constant ?
  • onlyican.com
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1589
  • Loc: Hants, UK

Post 3+ Months Ago

if someone had the ability to hack the website to echo a constant, they can probs include ur file and set the values again.
And ur password would bee last of your issues
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

The world could be destroyed by a huge asteroid at any second, but that doesn't mean we should stop eating.
  • onlyican.com
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1589
  • Loc: Hants, UK

Post 3+ Months Ago

we could all die by my mates rear end, doesn't mean.

No Actually, I am going to by a gas mask, that stuff is nasty.

Post Information

  • Total Posts in this topic: 13 posts
  • Users browsing this forum: No registered users and 71 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.