Jump to content

using a variable in a mysql query?


toxicityj
 Share

Recommended Posts

How do you use a variable in a mysql query? here's what I got

//connection crap here$del = $_POST[delete];mysql_query("DELETE FROM roster WHERE id='?????????'");

What would i put in place of my question marks to have it delete entries with the dynamically listed ID, which is acquired via $_POST ?Hope I make sense!Thanks.edit- I've tried just dumping the following in there and none of them worked-$_POST[delete]$del

Edited by JeremyD
Link to comment
Share on other sites

I'm not too focused on security at the moment. I'm very new to this stuff and more worried about making stuff work. What I'm working on right now is just a simple roster and content management system for my World of Wacraft guild website so security isn't a huge issue. I do, however, have all of the stuff that deals with writing to the database in a password protected directory just in case. I do plan to eventually learn how to make things more secure though.

Link to comment
Share on other sites

Stop doing that right NOW!Security is not something to be "bolted on" to something. You need to learn about the different security threats as they interfere with what you're doing.There are many things you don't need to worry about, but MySQL injection isn't one of them.How much of a pain is to just learn to use:

$del = mysql_real_escape_string($_GET['delete']);mysql_query("DELETE FROM roster WHERE id='$del'");

If don't want to learn why SQL injections are so dangerous just yet, fine... just make it a golden rule to use mysql_real_escape_string on ALL data that's about to be part of a MySQL query.

Link to comment
Share on other sites

Stop doing that right NOW!Security is not something to be "bolted on" to something. You need to learn about the different security threats as they interfere with what you're doing.There are many things you don't need to worry about, but MySQL injection isn't one of them.How much of a pain is to just learn to use:
$del = mysql_real_escape_string($_GET['delete']);mysql_query("DELETE FROM roster WHERE id='$del'");

If don't want to learn why SQL injections are so dangerous just yet, fine... just make it a golden rule to use mysql_real_escape_string on ALL data that's about to be part of a MySQL query.

does this apply to all SQL queries, regardless of the whether the source is from user input or a selected input on the page, like in the following example:say you had a website say where on the website there was a pulldown form input with a list of say oh, bands. and when you selected a band it took you a view artists page with all the info about that artist (via URL query string, i.e. domain.com/view_artist.php?artistName="The+Beatles"), so that the SQL for the view artist page looked something like this:
SELECT * FROM artists WHERE artistName = $GET['artistName']

Link to comment
Share on other sites

does this apply to all SQL queries, regardless of the whether the source is from user input or a selected input on the page, like in the following example:say you had a website say where on the website there was a pulldown form input with a list of say oh, bands. and when you selected a band it took you a view artists page with all the info about that artist (via URL query string, i.e. domain.com/view_artist.php?artistName="The+Beatles"), so that the SQL for the view artist page looked something like this:
SELECT * FROM artists WHERE artistName = $GET['artistName']

With GET values, the user can manually change the URL.With POST values, the user can type modify the page with Javascript in order to modify the value of the form fields.
Link to comment
Share on other sites

A lot of crackers don't even use normal browsers. Even a sloppy programmer can build a toy that makes HTTP requests without rendering anything. With this ability you can break a lot of security rules that browser makers have agreed to follow. This simplifies the cracking process a lot.

Link to comment
Share on other sites

Yup. Cookies too. Anything that's part of an HTTP request can be manipulated to mess with your stuff. That includes dumb stuff you might (for some reason) want to use, like the user-agent, and some of other stuff in the $_SERVER array.

Edited by Deirdre's Dad
Link to comment
Share on other sites

It's not the language's responsibility to make sure the application is secure, it's the programmer's responsibility. The language will do whatever you tell it to do, it's your responsibility to make sure that you're telling it to do things in a secure way.

Link to comment
Share on other sites

I'm curious-- why does an insecure version of that even exist?!
Because the query itself is text.... a string... and you are free to form strings in any way you like, including the gathering of raw user inputted data.If you had to explicitly use a series of functions (or something like that) to construct a query, your code will get much longer, but the worse part is that it will only be limited to what PHP supports. Allowing you to enter any query means that the burden is shifted to SQL instead - you can write any query as long as MySQL supports it.Technically, even with prepared statements, you can (in theory) append data as you do without prepared statements. In practice, doing that is pointless of course.mysql_real_escape_string() does what it says it does - it escapes any characters in the given string that would make MySQL treat the given string as anything more than a plain string.... in other words, it ensures that whatever you give to it can safely be used as a string value in MySQL.
Link to comment
Share on other sites

The link in Post #5 shows you how SQL injection works. Most of it depends on malicious users putting quotation marks in key locations of form data and changing the text of your query.When the quotation marks are escaped, they lose their ability to delimit data strings and are treated as ordinary characters. That's how mysql_real_escape_string() shuts down the injection.Every system trades off utility for vulnerabilities, same as you trade security in your home for windows you can see through and open. A good system also gives you ways to protect your weak spots. You can always do a search-and-replace for the dangerous characters. mysql_real_escape_string() just saves you the step.

Link to comment
Share on other sites

Okay I'm trying to figure out how to add security to what I've been working on. Technically the only two people that have access to anything that writes to a database is me and a friend I trust, but I may as well learn to do this from the get-go, regardless of need.My big question now is that instead of assigning all of my $_GET and $_POST 's variables, I've just been calling them in the mysql like so-

mysql_query("DELETE FROM roster WHERE id='$_GET[id]'");

Is it a bad idea to just use $_GET in the middle of my mysql without assigning it a variable?How would I protect that from an injection? Like this?

$_GET[id] = mysql_real_escape_string($_GET[id])

?

Edited by JeremyD
Link to comment
Share on other sites

If you do THAT, your $_GET variable will be useless in all other contexts.... well... it may look awkward in other contexts to be exact, but still.That's why people usually copy the data to a separate variable - if the $_GET variable is needed in another context, a new copy is created from the raw data, with the other escapings being done at that other point.An example of another common context is printing the data in HTML. If you just do something like:

echo '<div>' . $_GET['id'] . '</div>';

The user could input raw HTML code. As cool as this sounds at first, "raw HTML code" also includes JavaScript, and that in turn means he'll be able to do any manipulations on the page that he desires. This kind of attacks are known as XSS attacks. To protect from them, you need to escape the data with htmlspecialchars(), like, in the above example:

$id = htmlspecialchars($_GET['id']);echo '<div>' . $id . '</div>';

If you want to avoid the creation of a separate variable, you can simply do the escaping directly in the appropriate context. That's actually a good security practice if you ask me, because it means you'll never forget to do the appropriate escape, and still have your actual data. For example:

mysql_query("DELETE FROM roster WHERE id='" . mysql_real_escape_string($_GET['id']) . "'");

echo '<div>' . htmlspecialchars($_GET['id']) . '</div>';

BTW, the problem is not who has write access to the database, but which scripts have writing rights to the database (even if they don't use them presently). All of your PHP scripts, including the publically facing ones, have the right to write to the database. Even if they only read from it now, the fact that they could write if you just alter them means that the script must be protected.

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

×
×
  • Create New...