0

I have some issue with a sql query using quotes with variables. (In general I use "bind" so I don't have this kind of problem). Here's the query :

$myquery = mysql_query("SELECT * FROM mytable ORDER BY id ASC WHERE var='".$var."'");

The syntax seems not to be correct, can anybody help ?

Taryn
  • 242,637
  • 56
  • 362
  • 405
epsilones
  • 11,279
  • 21
  • 61
  • 85
  • 1
    Please, don't concat SQL strings... – Prinzhorn Oct 24 '12 at 10:20
  • 1
    This approach is usually prone to SQL injection attacks, so it is not really advisable. "Seems not to be correct" means absolutely nothing; are you getting an error? – lanzz Oct 24 '12 at 10:22
  • @lanzz, sorry not to be precise : in fact the query loads some data that I display on my webpage. But here it fails displaying this data – epsilones Oct 24 '12 at 10:25
  • 2
    Your query does not work because you have swapped the order of `ORDER BY` and `WHERE` clauses, not because of the quotes. If you fix the clause order, your query will work (albeit still vulnerable to injection), so there is no point to keep asking how to fix your quotes. – lanzz Oct 24 '12 at 10:38

3 Answers3

2

well you can try something like this:

$query = sprintf("SELECT * FROM mytable WHERE var='%s' ORDER BY id ASC",mysql_real_escape_string($var));
$result = mysql_query($query) or die("Error:" . mysql_error());

Also note that ORDER BY is at wrong place. It is more readable and you don't need to bother with single qoute concating. Also it is safe for mysql injection. Hope this helps!

alan978
  • 535
  • 3
  • 6
1

In general you should use the parameter binding features provided by your DBD (Database Driver for Perl) or other language and driver combination. I gather that you're using PHP (though you should tag your questions accordingly to remove the ambiguity.

Here's a StackOverflow thread on How to bind SQL parameters in PHP (using PDO). Note there are limitations to the PHP PDO::bindParam method as compared to similar features in other languages. So read the linked thread for caveats.

Here's another discussion about Binding Parameters to Statements ... for Perl (but conceptually applicable to other programming languages and their SQL libraries/drivers).

Community
  • 1
  • 1
Jim Dennis
  • 17,054
  • 13
  • 68
  • 116
  • I know, I always use the bind technique. Here I wondered about that because I am working on a project that uses massively quotes for the sql query, so I wanted to make it clear – epsilones Oct 24 '12 at 10:39
-1

You can use it like

$myquery = mysql_query("SELECT * FROM mytable ORDER BY id ASC WHERE var='$var'");
Miqdad Ali
  • 6,129
  • 7
  • 31
  • 50
  • Without evidence to the contrary, I assume `$var` has not been passed through `mysql_real_escape_string`, so this approach is prone to SQL injection. It also has the wrong `ORDER BY`/`WHERE` clause order present in the question, so it is still syntactically invalid. – lanzz Oct 24 '12 at 10:29
  • The solution of @alan978 worked fine, but your expression doesn't seem to work. But just by curiosity, does it exist some query using quotes, without using sprintf-style usage ? – epsilones Oct 24 '12 at 10:35
  • @lanzz , he is not told about the data, which we can assume, he is assign the value of data directly :P – Miqdad Ali Oct 24 '12 at 11:37
  • @Newben thats not mysql feature check it here http://www.trans4mind.com/personal_development/phpTutorial/quotes.htm – Miqdad Ali Oct 24 '12 at 11:39