Jump to content

sql-lover

Members
 View Profile  See their activity

  • Posts

    37

  • Joined

  • Last visited

    Never

Profile Information

  • Gender
    Not Telling

sql-lover's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. sql-lover
    Thanks for reply! In terms of query result of actual display, this is what I want -Show if they are different or not (top of the page) -Get all columns and display table A -Get all the columns and display table B This is how is right now ... -Get all columns and display table A -Get all the columns and display table B -Show if they are different or not (bottom of the page) My problem is, if I tried to show if they are different or not, it won't work, because that loop is using the variable I put to inside each table's loop.
  2. sql-lover
    Thanks. Since I posted this question I read additional material. Your comment makes sense.
  3. sql-lover
    [quote author=scootstah Not for SQL injection. Why, can you show (with my own code, of course)? not challenging your comment, but I want to fully understand your explanation.
  4. sql-lover
    Dummy question here, so please apologize my ignorance ... How many times I can invoke or call a query inside a PHP code if I'm using sqlsrv_fetch_array function? Here's the code (which works like a charm) <!DOCTYPE html> <html> <head> <title>Table Definition's Tool</title> <style type="text/css"> body { font-family: Arial, Verdana, sans-serif; color: #111111;} table { width: 600px;} th, td { padding: 7px 10px 10px 10px;} th { text-transform: uppercase; letter-spacing: 0.1em; font-size: 90%; border-bottom: 2px solid #111111; border-top: 1px solid #999; text-align: left;} tr.even { background-color: #efefef;} tr:hover { background-color: #c3e6e5;} .money { text-align: right;} </style> </head> <body> <?php //Variables $QueryResults1 = Array(); $QueryResults2 = Array(); $dbName=$_POST['dbName']; //Variable for the database name $PROinstance=$_POST['PRO']; //Variable for PRO SQL connection string $ITGinstance=$_POST['ITG']; //Variable for ITG SQL connection string $table=$_POST['table']; //Variable for the table $params = array($table); //Parameters for the T-SQL query, the table name here require 'ErrorHandling.php'; require 'ConnectionInfo.php'; ############################# ##Modify Date for PRO table## ############################# $mdateQuery="SELECT cast( cast (create_date as date) as date), cast( cast (modify_date as date) as date) FROM sys.tables WHERE name=?"; //The proper way is SELECT create_date, dateadd(dd,0, datediff(dd,0, modify_date)) FROM sys.tables WHERE name=?; $TableSchemaQuery = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?"; $mdatePROtable = sqlsrv_query($dbPRO, $mdateQuery, $params); if ($mdatePROtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } $mdateITGtable = sqlsrv_query($dbITG, $mdateQuery, $params); if ($mdateITGtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } ########################## ##Table Input Validation## ########################## //Main query to validate $validatingQuery ="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =?"; $validatingPROtable = sqlsrv_query($dbPRO, $validatingQuery, $params); if ($validatingPROtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } $validatingITGtable = sqlsrv_query($dbITG, $validatingQuery, $params); if ($validatingITGtable === false) { exitWithSQLError('Wrong table name or table does not exist.'); } while ($row1 = sqlsrv_fetch_array($validatingPROtable,SQLSRV_FETCH_NUMERIC)) { $PROtableName=$row1[0]; } while ($row1 = sqlsrv_fetch_array($validatingITGtable,SQLSRV_FETCH_NUMERIC)) { $ITGtableName=$row1[0]; } if ( empty($PROtableName) && empty($ITGtableName) ) { echo "There is no table on PRO or ITG with that name. Please validate with your POR dba."; } elseif (empty($PROtableName)) { echo "There is a table on ITG with that name but not on PRO. Please validate with your POR dba."; } elseif (empty($ITGtableName)){ echo "There is a table on PRO with that name but not on ITG. Please validate with your POR dba."; } //If table's name exists on both, do schema comparison inside following "else" statement else { /* Set up the query for PRO's table. */ // Run PRO query $qresult1 = sqlsrv_query($dbPRO, $TableSchemaQuery, $params); if ($qresult1 === false) { exitWithSQLError('Retrieving schema failed.'); } // Retrieve individual rows from PRO result echo "<h3>Table's structure for \"$table\" - PRO environment</h3>"; echo '<table>'; echo '<thead>'; echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; echo '</thead>'; echo '<tbody>'; $i=1; while ($row1 = sqlsrv_fetch_array($qresult1,SQLSRV_FETCH_NUMERIC)) { $i++; $QueryResults1[$row1['0']] = $row1; $QueryResults1[$row1['1']] = $row1; $QueryResults1[$row1['2']] = $row1; $QueryResults1[$row1['3']] = $row1; $QueryResults1[$row1['4']] = $row1; $QueryResults1[$row1['5']] = $row1; $QueryResults1[$row1['6']] = $row1; $QueryResults1[$row1['7']] = $row1; if ($i % 2) { echo '<tr class="even">'; echo '<td>' . htmlspecialchars($row1['0']) . '</td>'; echo '<td>' . htmlspecialchars($row1['1']) . '</td>'; echo '<td>' . htmlspecialchars($row1['2']) . '</td>'; echo '<td>' . htmlspecialchars($row1['3']) . '</td>'; echo '<td>' . htmlspecialchars($row1['4']) . '</td>'; echo '<td>' . htmlspecialchars($row1['5']) . '</td>'; echo '<td>' . htmlspecialchars($row1['6']) . '</td>'; echo '<td>' . htmlspecialchars($row1['7']) . '</td>'; echo '</tr>'; } else { echo '<tr>'; echo '<td>' . htmlspecialchars($row1['0']) . '</td>'; echo '<td>' . htmlspecialchars($row1['1']) . '</td>'; echo '<td>' . htmlspecialchars($row1['2']) . '</td>'; echo '<td>' . htmlspecialchars($row1['3']) . '</td>'; echo '<td>' . htmlspecialchars($row1['4']) . '</td>'; echo '<td>' . htmlspecialchars($row1['5']) . '</td>'; echo '<td>' . htmlspecialchars($row1['6']) . '</td>'; echo '<td>' . htmlspecialchars($row1['7']) . '</td>'; echo '</tr>'; } } echo '</tbody>'; echo '</table>'; echo '<br>'; // null == no further rows, false == error if ($row1 === false) { exitWithSQLError('Retrieving schema failed.'); } while ( $row1 = sqlsrv_fetch( $mdatePROtable)) { echo "<p>Created on " .sqlsrv_get_field( $mdatePROtable, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))." and last time modified on ".sqlsrv_get_field( $mdatePROtable, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))."</p>"; } /* Set up the query for ITG's table. */ $qresult2 = sqlsrv_query($dbITG, $TableSchemaQuery, $params); if ($qresult2 === false) { exitWithSQLError('Query of product data failed.'); } // Retrieve individual rows from ITG result echo "<h3>Table's structure for \"$table\" - ITG environment</h3>"; echo '<table>'; echo '<thead>'; echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; echo '</thead>'; echo '<tbody>'; $i=1; while ($row2 = sqlsrv_fetch_array($qresult2,SQLSRV_FETCH_NUMERIC)) { $i++; $QueryResults2[$row2['0']] = $row2; $QueryResults2[$row2['1']] = $row2; $QueryResults2[$row2['2']] = $row2; $QueryResults2[$row2['3']] = $row2; $QueryResults2[$row2['4']] = $row2; $QueryResults2[$row2['5']] = $row2; $QueryResults2[$row2['6']] = $row2; $QueryResults2[$row2['7']] = $row2; if ($i % 2) { echo '<tr class="even">'; echo '<td>' . htmlspecialchars($row2['0']) . '</td>'; echo '<td>' . htmlspecialchars($row2['1']) . '</td>'; echo '<td>' . htmlspecialchars($row2['2']) . '</td>'; echo '<td>' . htmlspecialchars($row2['3']) . '</td>'; echo '<td>' . htmlspecialchars($row2['4']) . '</td>'; echo '<td>' . htmlspecialchars($row2['5']) . '</td>'; echo '<td>' . htmlspecialchars($row2['6']) . '</td>'; echo '<td>' . htmlspecialchars($row2['7']) . '</td>'; echo '</tr>'; } else { echo '<tr>'; echo '<td>' . htmlspecialchars($row2['0']) . '</td>'; echo '<td>' . htmlspecialchars($row2['1']) . '</td>'; echo '<td>' . htmlspecialchars($row2['2']) . '</td>'; echo '<td>' . htmlspecialchars($row2['3']) . '</td>'; echo '<td>' . htmlspecialchars($row2['4']) . '</td>'; echo '<td>' . htmlspecialchars($row2['5']) . '</td>'; echo '<td>' . htmlspecialchars($row2['6']) . '</td>'; echo '<td>' . htmlspecialchars($row2['7']) . '</td>'; echo '</tr>'; } } echo '</tbody>'; echo '</table>'; echo '<br>'; while ( $row1 = sqlsrv_fetch( $mdateITGtable)) { echo "<p>Created on " .sqlsrv_get_field( $mdateITGtable, 0, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))." and last time modified on ".sqlsrv_get_field( $mdateITGtable, 1, SQLSRV_PHPTYPE_STRING( SQLSRV_ENC_CHAR))."</p>"; } // null == no further rows, false == error if ($row2 === false) { exitWithSQLError('Retrieving schema failed.'); } //Comparing Arrays foreach ($QueryResults1 as $key => $value) { if ($QueryResults2[$key]!=$value) { echo '<p><span style="BACKGROUND-COLOR: #ffff00">The schemas are different!</span></p>'; break; } } $PROTableSize=sizeof($QueryResults1); $ITGTableSize=sizeof($QueryResults2); if ($PROTableSize==$ITGTableSize){ echo "<p>Schemas are identical!</p>\n"; } // Releasing resources and closing connections sqlsrv_free_stmt($qresult1); sqlsrv_free_stmt($qresult2); } // Releasing resources and closing connections sqlsrv_free_stmt($validatingITGtable); sqlsrv_free_stmt($validatingPROtable); sqlsrv_close($dbPRO); sqlsrv_close($dbITG); ?> </body> </html> Not the most elegante code, I'm still learning PHP, but this is what it does: - [*]Use a T-SQL query to get the table's schema of a given table [*]Run same query on two different MS-SQL instances [*]Display result for each one [*]Compare results to see if they match The problem I have (logic, not syntax) is that I want the comparison's result to be display 1st or way on the top. But if I do that, won't work, as I am capturing the result of each query or array on some variables, $QueryResults1 and $QueryResults2. If I try to run the while plus sqlsrv_fetch_array twice (1st to capture the results, 2nd to actually display) it gives me an error or does not display anything. How many times can I call sqlsrv_fetch_array inside a PHP code? Or, how can I compare both results so the echo statement can appear way on top? Hope I explain it correctly, thanks in advance
  5. sql-lover
    They are essentially the same. Your just passing the parameters and sql together in a call vs doing it separately by preparing the query first then binding parameters later. I guess that my question is still there :-) Do I have to sanitize the input? And if answer is Yes, which function or function will help me now than I'm using the Microsoft connector or driver.
  6. sql-lover
    "blonde moment" You're right. Prepared isn't the same as parameterized. But I would prefer not to redesign the whole code and change to prepared if using a parameter is still safe.
  7. sql-lover
    Thanks for reply. isn't prepare statement the same as parametrized queries? but anyway, my question is about above code. Do I have to sanitize after using a parameterized query?
  8. sql-lover
    Hi, I am using parameterized queries on my code, here's the relevant part $params=$_POST['ITGtable']; $tsql2 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?"; /* Execute the statement with the specified parameter value. Display the returned data if no errors occur. */ $stmt2 = sqlsrv_query( $conn, $tsql2, $params); if( $stmt2 === false ) { echo "Statement 2 could not be executed.\n"; die( print_r(sqlsrv_errors(), true)); } else { $qty = sqlsrv_fetch_array( $stmt2); } Do I really have to sanitize $_POST['ITGtable'] for apostrophe, semicolon, etc, to avoid SQL injection problems? Or just with above code I should be safer (I did not say safe) against SQL injection? And if the answer is "No", what could be the sanitize code of function? I am using sqlsrv and MS-SQL database engine; most of the functions we have for sanitize inputs on MySQL are not available for MS-SQL. Thanks in advance,
  9. sql-lover
    1st, Thanks for the MOD which moved it to the right forum :-) Now, to dmikester1, appreciate your response. My CSS knowledge is very limited, but I used to code pure HTML back in 1994 (yeah, maybe I'm older than you, lol) so I can catch up concepts quickly. I'm actually a professional DBA. What I am trying to accomplish is make a clear distinction between the headers and the content. Also, I would like to experiment with some type of grid, like creating a table? So results can be easier to read. Not so sure how it will look like, that's why I want to play a little bit. But I don't know how to generate such grid. However, you gave me a basic idea of how change color for text and headers.
  10. sql-lover
    Hi, Need some advises or quick help with my query result layout (attached to this post) I'm currently using this code to organize and show my query's result echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; // Retrieve individual rows from the result while ($row1 = sqlsrv_fetch_array($qresult1,SQLSRV_FETCH_NUMERIC)) { $QueryResults1[$row1['0']] = $row1; $QueryResults1[$row1['1']] = $row1; $QueryResults1[$row1['2']] = $row1; $QueryResults1[$row1['3']] = $row1; $QueryResults1[$row1['4']] = $row1; $QueryResults1[$row1['5']] = $row1; $QueryResults1[$row1['6']] = $row1; $QueryResults1[$row1['7']] = $row1; echo '<tr><td>', htmlspecialchars($row1['0']), '</td><td>', htmlspecialchars($row1['1']), '</td><td>', htmlspecialchars($row1['2']), '</td><td>', htmlspecialchars($row1['3']), '</td><td>', htmlspecialchars($row1['4']), '</td><td>', htmlspecialchars($row1['5']), '</td><td>', htmlspecialchars($row1['6']), '</td><td>', htmlspecialchars($row1['7']), "</td></tr>\n"; } and echo '<tr><th>NAME</th><th>TYPE</th><th>POSITION</th><th>DEFAULT</th><th>LENGHT</th><th>IS NULLABLE</th><th>NUMERIC PRECISION</th><th>NUMERIC SCALE</th></tr>'; // Retrieve individual rows from the result while ($row2 = sqlsrv_fetch_array($qresult2,SQLSRV_FETCH_NUMERIC)) { $QueryResults2[$row2['0']] = $row2; $QueryResults2[$row2['1']] = $row2; $QueryResults2[$row2['2']] = $row2; $QueryResults2[$row2['3']] = $row2; $QueryResults2[$row2['4']] = $row2; $QueryResults2[$row2['5']] = $row2; $QueryResults2[$row2['6']] = $row2; $QueryResults2[$row2['7']] = $row2; echo '<tr><td>', htmlspecialchars($row2['0']), '</td><td>', htmlspecialchars($row2['1']), '</td><td>', htmlspecialchars($row2['2']), '</td><td>', htmlspecialchars($row2['3']), '</td><td>', htmlspecialchars($row2['4']), '</td><td>', htmlspecialchars($row2['5']), '</td><td>', htmlspecialchars($row2['6']), '</td><td>', htmlspecialchars($row2['7']), "</td></tr>\n"; } Of course, that's the relevant part of the code. The query and connection not included. I would like to maybe use css, better fonts or even color. It actually looks boring and ugly. What markup code should I use or how can I modify that part of the code so I can get a more professional or easy to read display?
  11. sql-lover
    digibucc Thank you so much, appreciate the quick reply!
  12. sql-lover
    Hi, While trying to create my own script that allows you to compare two different files , found this amazing algorithm from Paul Butler function diff($oldhistory, $newhistory){ foreach($oldhistory as $oindex => $ovalue){ $nkeys = array_keys($newhistory, $ovalue); foreach($nkeys as $nindex){ $matrix[$oindex][$nindex] = isset($matrix[$oindex - 1][$nindex - 1]) ? $matrix[$oindex - 1][$nindex - 1] + 1 : 1; if($matrix[$oindex][$nindex] > $maxlen){ $maxlen = $matrix[$oindex][$nindex]; $omax = $oindex + 1 - $maxlen; $nmax = $nindex + 1 - $maxlen; } } } if($maxlen == 0) return array(array('d'=>$oldhistory, 'i'=>$newhistory)); return array_merge( diff(array_slice($oldhistory, 0, $omax), array_slice($newhistory, 0, $nmax)), array_slice($newhistory, $nmax, $maxlen), diff(array_slice($oldhistory, $omax + $maxlen), array_slice($newhistory, $nmax + $maxlen))); } function htmlDiff($oldhistory, $newhistory){ $diff = diff(explode(' ', $oldhistory), explode(' ', $newhistory)); foreach($diff as $k){ if(is_array($k)) $ret .= (!empty($k['d'])?"<del>".implode(' ',$k['d'])."</del> ":''). (!empty($k['i'])?"<ins>".implode(' ',$k['i'])."</ins> ":''); else $ret .= $k . ' '; } return $ret; } This is embarrassing, but how can I test this with two text variables? Where should I wrote the echo line to display some output? I'm new to PHP. Also, my own and extremely simplify idea is using strtoupper and md5 for comparing two texts. Is that a good logic? The main goal of my program is able to compare two MS-SQL store procedures from two different servers and tell if they both are the same of different versions. Thanks in advance,
  13. sql-lover
    Ohh, thats not a problem :-) ... I can post the whole code again. I'm right now focus on the input validation and security part.
  14. sql-lover
    Thanks, fixed .... this way ... $params = array($table); Very silly mistake, but thanks again for pointing that out!
  15. sql-lover
    Trying to make my code more secure. This is what I currently have, which is not secure by any means: $query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$table'"; // Run PRO query $qresult1 = sqlsrv_query($dbPRO, $query1); if ($qresult1 === false) { exitWithSQLError('Retrieving schema failed.'); } This is how I changed it, $query1 = "SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=?"; $params = array(1, $table); // Run PRO query $qresult1 = sqlsrv_query($dbPRO, $query1, $params); if ($qresult1 === false) { exitWithSQLError('Retrieving schema failed.'); } but I'm getting this error: SQL-Status: 22018 Code: 245 Message: [Microsoft][sql Server Native Client 10.0][sql Server]Conversion failed when converting the nvarchar value 'sysrscols' to data type int Please notice I am using sqlsrv_query function because my database engine is MS-SQL 2008. That's why I'm a bit confused. Most documentation online is pointed to MySQL. exitWithSQLError is a customized function of mine, so please ignore. Any help or hints is appreciated, Thanks,
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.