Strip Characters

[NXArmada]

I have 2 MySQL Databases and I want to link them together.

What I am trying do is when you brin up a record say record ID in one DB is RMA-4598 and in the other the record ID is 4598. What I want to do is Strip the RAM- off so im left with 4598 and have the 4598 place into a mySQL query string



<?
mysql_connect("localhost","root","rootpass");
mysql_select_db("service");

if($_GET["cmd"]=="view" || $_POST["cmd"]=="view")
{
$id = $_GET["id"];
$sql = "SELECT * FROM service WHERE id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
}
?>


The RMA field for the DB code above is
<?php echo $myrow["svc"] ?>

So i want to strip RMA- from the SVC number and use it as the ID for another DB


<?
mysql_connect("localhost","root","rootpass");
mysql_select_db("service");

if($_GET["cmd"]=="view" || $_POST["cmd"]=="view")
{
$id = $_GET["id"];
$sql = "SELECT * FROM rma WHERE id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
}
?>

Once i get that down i should be able to load the Data From both DB's and display on screen.

[mwinter]

I have 2 MySQL Databases and I want to link them together.It really is in your best interest to use the right terms: you have two tables or relations in a single database.


What I am trying do is when you brin up a record say record ID in one DB is RMA-4598 and in the other the record ID is 4598. What I want to do is Strip the RAM- off so im left with 4598 and have the 4598 place into a mySQL query stringUse the built-in SQL SUBSTRING(str [FROM off [FOR len]]) function. In your example,



SELECT SUBSTRING('RMA-4598' FROM 5)
would return '4598'.

To perform a join, you could use something like:



"SELECT * FROM rma WHERE id=SUBSTRING((SELECT svc FROM service WHERE id=$id) FROM 5)"
The subquery finds the tuple containing the matching id value, and returnings the value from the 'svc' attribute. The SUBSTRING function then removes the first four characters ('RMA-') and that value is then used in the condition for the outer query.




$id = $_GET["id"];
$sql = "SELECT * FROM service WHERE id=$id";
I really hope that you aren't using data sent with the request directly within a SQL query. That's asking for trouble.

Mike

[NXArmada]

This is for an Intranet.

But thanks i well try our what you posted.

[NXArmada]

Thanks mwinter that was exactly what i wanted.

[Twey]

This is for an Intranet.That's still asking for trouble, unless you happen to trust every single user (and potential user) of that intranet with all the data in that database, and with the power to insert, alter and delete data. That's the sort of privileges the perpetrator of an SQL injection attack can expect from most SQL setups. It is possible to restrict SQL users' permission to just that necessary, but I doubt you've done that; in any case, it's still more power than you really want to entrust to people you don't know.

[NXArmada]

Well the pages that the user see dont have any fields to change it just displaying a record for print you can't change anything.

[Twey]

Doesn't matter, it's still being passed from the browser to the server (using GET, even worse), and so can be arbitrary content.

[NXArmada]

what would you suggest?

[Twey]

Firstly, you need to keep a reference to the connection. You should have done this anyway.
$conn = mysql_connect("localhost","root","rootpass");
You can then encode the query:
$id = mysql_real_escape_string($_GET["id"], $conn);

[NXArmada]

Thanks Twey i well give that a try.

[Twey]

Oh, and another thing: why on Earth are you using the root account to access the database?!
You know that whole privilege-seperation thing I was going on about? Well... using the root account for anything but manual administrative tasks is just about the worst security flaw you can possibly subject your poor database to.

[NXArmada]

its all Prototype right now

[Twey]

Doesn't matter, there's no reason to be using the root account.

[djr33]

I'm also coding something that uses get (and post) data.

I have a submit form that will add info to a database.

There's something wrong with just letting people type something in the form, click submit then do that?

How about my dynamic page that uses get to get some html from a database? (index.php?page=5, where it would search the database for the row WHERE id=5, then get the html data from that row and display it)

Isn't this kinda the point of mysql? It's a security risk?

[Twey]

Isn't this kinda the point of mysql? It's a security risk?It is, yes. This is what mysql_real_escape_string() is for.
How about my dynamic page that uses get to get some html from a database? (index.php?page=5, where it would search the database for the row WHERE id=5, then get the html data from that row and display it)And what if someone used:
index.php?page=1;DROP%20DATABASE%20yourdbIf the query string looked like this:
$sql = "SELECT * FROM table WHERE id=$id;";then after it had been parsed, it would look like this:
$sql = "SELECT * FROM table WHERE id=1;DROP DATABASE yourdb;";... and your script would faithfully execute it. If you used mysql_real_escape_string, it would escape the semicolon with a backslash, rendering it harmless and simply returning no results.

[djr33]

mysql_real_escape_string is just something you do to an ordinary string?
doesn't have to be part of the query or anything, right?
I mean... I can do it before connecting to or doing anything with the database, yes?

//adding now.

[mwinter]

And what if someone used:
index.php?page=1;DROP%20DATABASE%20yourdbYour point is valid, of course, but the answer to that question should be: not a lot.

CGI programs (and the like) should consider semicolons (&#59;) to be equivalent to ampersands (&) for the purpose of separating query string name/value pairs, so the value of $_GET['page'] will equal '1' in PHP.

Replace ';' with '%3B' and now you're in trouble. :)


If you used mysql_real_escape_string, it would escape the semicolon with a backslash, rendering it harmless and simply returning no results.True, though that's overkill in this case. As the value of 'page' should only be a number, this can be validated. Non-numeric strings, such as an attempted injection attack, can be rejected. No need for escaping.



mysql_real_escape_string is just something you do to an ordinary string?Yes. After creating a connection (the function uses the connection to determine character encoding), you'd call the function with data you intend to add to the SQL query, and append or insert the return value.


doesn't have to be part of the query or anything, right?No, it mustn't.


I can do it before connecting to or doing anything with the database, yes?No, you must connect first.

Mike

[djr33]

Thanks.

Yeah, I figured out where to put it by playing with it.

Easy, and effective... :)

It's setup now.

by the way, while I used a number my example, the page is actually like "index.php?page=name"... a string. So... yeah, it is important to validate, I guess :)

[Twey]

No, you must connect first.Well, technically the connection-handler argument is optional, so you can just call it on the string to encode it using the default system character encoding. However, it's not a good idea, since this would reduce portability and could cause problems if MySQL's character encoding was different to that of the system.

[mwinter]

Well, technically the connection-handler argument is optional [...]The argument can be omitted from the call, but a connection isn't optional. From the manual page (http://uk.php.net/mysql-real-escape-string):


link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level warning is generated.This applies to many of the MySQL functions.

Mike

[Twey]

Well, that'll teach me to skim-read. :p