Solved

Putting vairables into database

Posted on 2004-09-18
9
228 Views
Last Modified: 2012-08-13
Hi,

Im trying to use this bit of code to put some variables into the database, it puts data into the db ok no problems, but it comes up as the vaiable names within the database i.e. $password in the password field etc, whats up with this ?

$sql = 'INSERT INTO `clients` ( `client_name` , `client_email` , `cms_clientid` , `cms_password` ) '
        . ' VALUES ( \'$clientname\', \'$email\', \'$userid\', \'\$password\' );'
        . ' ';
$result = mysql_query($sql);

Thanx
0
Comment
Question by:MirageSF
  • 3
  • 3
  • 3
9 Comments
 
LVL 27

Accepted Solution

by:
Diablo84 earned 25 total points
ID: 12092523
You need double quotes around the query not single quotes in this case, so for example:

$sql = "INSERT INTO `clients` (`client_name`,`client_email`,`cms_clientid`,`cms_password`)"
        ." VALUES ('$clientname','$email','$userid','$password')";
$result = mysql_query($sql);
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 12092526
Assuming your fields and variables are ok, I think this will run ok:

$sql = "INSERT INTO `clients` ( `client_name` , `client_email` , `cms_clientid` , `cms_password` ) ".
          "VALUES ('".$clientname."', '".$email."', '".$userid."', '".$password."' );";


Notice there are single and double quotes.
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12092577
Re: Jaime olivares.

As i might note, you are actually using singlequote where doublequotes should/could be used and vice versa.

If you enclose a string in single quotes. PHP won't check if there is any php within the string (Vars/functions). When you enclose them in double quotes, php will check and replace the variables if available.

e.g. $sql = "values ('".$clientname."')" is quite 'useless' (useless is a very strong word in this matter, but I couldn't find another phrase to say what I wanted to say).

In the code above, php will check the piece "values ('" for php vars/functions, conclude they are not there, concattenate $clientname en then check the "')" piece for variables.

When using $sql = 'values("'.$clientname.'")'; php will notice the single quotes and just pass by it, not checking for variables. This will be the quicker code of the two.

Including some security into the code, my suggestion would be something like:
$sql = 'INSERT INTO
          `clients` (`client_name`, `client_email`, `cms_clientid`, `cms_password`)
          VALUES (
          "'.addSlashes($clientname).'"
          ,"'.addSlashes($email).'"
          ,"'.addSlashes($userid).'"
          ,"'.addSlashes($password).'"
          )';

or (note the intval below)

$sql = 'INSERT INTO
          `clients` (`client_name`, `client_email`, `cms_clientid`, `cms_password`)
          VALUES (
          "'.addSlashes($clientname).'"
          ,"'.addSlashes($email).'"
          ,'.intval($userid).'
          ,"'.addSlashes($password).'"
          )';

-r-.
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 12092724
I know my solution works because I use it in all my PHP programs without problems. Since I am a former Basic/C programmer, I don't like to insert variables or functions into strings, because them are a bit harder to identify. So it is **useful** because contribute to legibility.

BTW. In fact I preferm this style because is more readable:

$sql = "INSERT INTO clients SET " .
               "client_name='".$clientname."', " .
               "client_email='".$email."', " .
               "cms_clientid='".$userid."', " .
               "cms_password='".$password."');";


0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 49

Expert Comment

by:Roonaan
ID: 12092828
I understand you are used to it, but when you are using such a syntax that your variables are always excluded from your string, you might want to interchange the singles with the doubles and add just that tiny bit of performance improvement to your code.

And use addslashes/mysql_escape_string in case someone uses "'); drop table `clients`;" as a password.
0
 
LVL 27

Expert Comment

by:Diablo84
ID: 12093016
I have never taken much to the issue of performance in such small scripts, the difference we are talking about is a matter of milliseconds and the user won't so much as notice a difference, in fact its likely that the only thing that will notice is a benchmarking script. When it comes to more important things such as regular expressions you should pay attention to efficent patterns or better still just stick to string functions where possible but for things such as this its personal preference.

Every coder writes their scripts in a certain way according to their preference making their code tidy and readible so they can work with it the way they like, as long as it executes within a practical amount of time i do not see the problem.
0
 
LVL 49

Expert Comment

by:Roonaan
ID: 12093077
Isn't EE just the place to mention these kind of optimizations?

Although results are limited as you say correctly, even learning to use quotes in the correct way is a good habit to have, isn't it? Even personal preference could be based on some choices you've made in the past, and the knowledge/experience you have.

I support your statement that most of the performance can be increased in queries and regexp's but this - in my opinion - should not exclude trying to gain performance in the smaller ways, one of which is using single quotes if you are already accustomed not to use your variables within strings.

Just my 2 cts.

-r-
0
 
LVL 27

Expert Comment

by:Diablo84
ID: 12093195
>>  Isn't EE just the place to mention these kind of optimizations?

Sure, but its also the place to share an opinion where appropriate :)

>> learning to use quotes in the correct way is a good habit to have

Its not necessarily the correct way, it may be the preferred way but that doesn't mean to say the alternative ways are incorrect. I am all for demonstrating the various methods (and even stating why one may be better then another) to users but at the end of the day i think its best to leave the door open on all of the options so they can go with the method that works best for their situation... or just the one that they like best.

If there is a specific and justified reason why one method should be used in place of another then i would say so however in this particular case i - also in my personal opinion - don't regard the small increase in performance to be a justified reason to switch from a preferred method (in the case of jaime_olivares) to a less preferred method.

When i write queries which include variable i use the method you opt for, but not because of performance, just simply because the code looks tidier - so personal preference :)

Taking another interesting example a friend of mine uses the method jaime_olivares uses, the reason - he codes in Dreamweaver which as you may know features code highlighting. If you write the variables straight into the query then they blend in with the rest of the line so the whole query is red (the colour for strings). However, if the variables are concatenated then they display black within the string query. As you may have guessed this means you have a red line with black variable names within it making the code much easier to read and follow.

Another example is handling arrays or super globals within the query, i always concatenate these like so:

mysql_query("SELECT * FROM sometable WHERE somefield = '".$_POST['somevar']."'");

Others may chose to use the alternative method (which is theoretically better for performance) being:

mysql_query("SELECT * FROM sometable WHERE somefield = '{$_POST['somevar']}'");

So i guess to summarise in the case of this kind of thing i don't regard the performance issue to be a cause for concern so i stick to ways i like best. If there is a case when tight code is essential then i would write the script the best way possible otherwise i don't intend to sacrifice valuable moments of my time changing my methods for the sake of a few milliseconds that the users won't notice.

I hadn't intended to type that much, I think it must be time for another coffee :)

0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 12093245
>If you write the variables straight into the query then they blend in with the rest of the line so the whole query is red (the colour for strings). However, if the variables are concatenated then they display black within the string query.
That's a good reason because I use HTML-Edit, which have syntax coloring.

Some other reason:
It is very confusing to me to quote variables with single quote, because in many programming languages strings are quoted with double quotes and single characters with single quotes.

There is no significative performance issue. PHP will spend a few microsecond in interpret the string chain. And many milisecond in attend the SQL itself.
In electronics engineering, when you have to "model" a formula, you don't sum "insignificative" variables to simplify it. The final effect: THE SAME. So, I don't use to **take care** about this equation elements.

But as Diablo and I have said, it just a matter of preferences.
Best regards,
Jaime.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now