Can't update php database with a flash application

Question

I am having some problem here. I am trying to develop a flash database manager for my company, and I already have the insert and "search" functions working okay. The problem comes up when trying to get the UPDATE working. Ill post both codes here:

PHP (UPDATED)

<?php
//connect to the local MySQL
$connect=mysql_connect("localhost", "****", "****");

//select your database
mysql_select_db("****");

//Variables
$ID=$_POST[IDPost];

$Nome=$_POST[Nome];
$Tipo=$_POST[Tipo];
$Empresa=$_POST[Empresa];
$Morada=$_POST[Morada];
$CodPostal=$_POST[CodPostal];
$Email=$_POST[Email];
$Contacto1=$_POST[Contacto1];
$Contacto2=$_POST[Contacto2];
$DataNascimento=$_POST[DataNascimento];
$Profissao=$_POST[Profissao];
$Notas1=$_POST[Notas1];
$Notas2=$_POST[Notas2];

//query the database
$query="

UPDATE 
    GestaoClientes 
SET 
    Nome = '$Nome',
    Tipo = '$Tipo',
    Empresa = '$Empresa',
    Morada = '$Morada',
    CodPostal = '$CodPostal',
    Email = '$Email',
    Contacto1 = '$Contacto1',
    Contacto2 = '$Contacto2',
    DataNascimento = '$DataNascimento',
    Profissao = '$Profissao',
    Notas1 = '$Notas1',
    Notas2 = '$Notas2'
WHERE 
    ID = '$ID'";

$result=mysql_query($query);

if (!mysql_query($query,$connect))
{
    die('Error: ' . mysql_error());
    echo "Result=NotOk";
}else{
    echo "Result=Ok";
}

mysql_close($connect);
?>

Flash

public function editInfo(MouseEvent):void
        {
            var request:URLRequest = new URLRequest ("link.php");
                request.method = URLRequestMethod.POST; 
                trace("called");

                var variables:URLVariables = new URLVariables(); 

                variables.IDPost = NField.text;

                variables.Nome = NomeField.text;
                variables.Email = NomeField.text;
                variables.Morada = MoradaField.text;
                variables.CodPostal = CodPostalField.text;
                variables.Tipo = TipoField.text;
                variables.Empresa = EmpresaField.text;
                variables.Profissao = ProfissaoField.text
                variables.DataNascimento = DataNascimentoField.text;
                variables.Notas1 = Notas1Field.text;
                variables.Notas2 = Notas2Field.text;

                request.data = variables; 

                var loader:URLLoader = new URLLoader (request); 
                loader.addEventListener(Event.COMPLETE, onComplete); 
                loader.dataFormat = URLLoaderDataFormat.VARIABLES; 
                loader.load(request); 

            function onComplete(e:Event):void 
            {
                trace("ok");
            }

}

When I try going to the php in the browser if just gives me the error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Nome' = '', 'Tipo' = '', 'Empresa' = '', 'Morada' = '', 'CodPostal' = '', 'Emai' at line 4

This although is probably normal, since Im not passing any "POST" variables through the browser.

The flash doesent return any errors when trying this code, so I assume the connection itself is okay, but it doesent do the update either. Is there something wrong with this code? Thanks.

UPDATE: I now changed my code, and it does not show the syntax error, but still doesent update within the flash. Any ideias why? :/ thanks

you should be escape all your $_POST variables before trying to do an SQL quey with them. — Twelve47, Apr 12 '11 at 14:15
What is a "PHP database"? Where is your SQL Injection prevention? — Lightness Races in Orbit, Apr 12 '11 at 14:15
@Marco, your PHP is horrifying. You are wide open to numerous attacks, and probably some rough data is breaking your query. You should implement PDO for your DB work. See http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html — Brad, Apr 12 '11 at 14:17
@Brad what he is doing can be done perfectly fine, and safely, using the `mysql_*` family of functions. There's no pressing need to switch PDO for a small Flash helper tool — Pekka, Apr 12 '11 at 14:17
I am very newby with this, and Im just trying to get it to work as easily as I can, So I have no ideia what you mean with sql injection prevention. o_o On another note, I occulted the Database name and pass, but I believe its working, as I said, flash does not return any error when calling it, and I have already done those in INSERT and normal SELECT. And excuse me, im just starting out, just grabbed some tuturials off the web and went from there. — FoxLift, Apr 12 '11 at 14:21
@Pekka, sure, but since he is just learning, he might as well learn methods that avoid the issue (the specific issue of injection, not everything of course) altogether. Also, see http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html — Brad, Apr 12 '11 at 14:21
@Brad that link is outdated, the vulnerability applies only if the connection encoding isn't properly set. See http://stackoverflow.com/questions/5288953/is-mysql-real-escape-string-broken plus, PDO alone will not automatically protect from every conceivable SQL injection issue and can create a false sense of security. Using it still requires thinking, and with thinking, you can be safe with mysql_* as well. PDO is still the superior library, no doubt. — Pekka, Apr 12 '11 at 14:24
@Marco can you show the full PHP code? The query part is missing now. — Pekka, Apr 12 '11 at 15:12
Done. I pasted over that part when updated, sorry. And thanks for helping me out :) — FoxLift, Apr 12 '11 at 15:16
Okay, seems to be working now! Sorry for wasting your time guys, and I will take a look at SQL injections prevention when I finish the main codes. Thanks :) — FoxLift, Apr 12 '11 at 15:38

score 3 · Accepted Answer · edited May 23 '17 at 11:55

3

You need to use backticks instead of single quotes for column names:

`Nome`

this is the reason for the syntax error. It is also possible to use no quotes at all.

Also, your code is vulnerable to SQL injection. Read up on the issue, it's essential for security.

To fix the vulnerability at hand, do the following on every variable:

$Nome = mysql_real_escape_string($_POST["Nome"]);

and then insert the escaped variable:

SET `Nome` = '$Nome',

edited May 23 '17 at 11:55

Community

1
1

answered Apr 12 '11 at 14:15

Pekka

442,112
142
972
1,088

I have now changed the code and updated the question. Could you please take a look at it and try to see whats up with it? The query appears to have been fixed, but flash still doesent update.. – FoxLift Apr 12 '11 at 15:11

score 1 · Answer 2 · answered Apr 12 '11 at 14:16

I suggest you take a close look at escaping your external input! Inserting variables directly into your query exposes you to injection, which is an enormous security issue. (read this).

the problem you have is that you use single quotes around the field names, this is incorrect.

MySQL uses backticks ( ` ), but I do not recommend using those since they limit portability to other sql applications.

score 1 · Answer 3 · answered Apr 12 '11 at 14:16

Remove the single quotes around the column names. Backticks (`) are allowed, single quotes (') are not.

I hope you realize that if your code really looks like above you have a massive security hole in your application, as anyone can execute arbitrary sql code.

Can't update php database with a flash application

3 Answers3